indexlevels
Jacques wanted a faster way to find the number of B-tree levels in a table's indexes, since "oncheck -pT dbname" was slow and produced huge output he had to parse with grep/awk. Several people answered with the same simple solution: query the system catalogs, joining sysindexes to systables on tabid and selecting the 'levels' column (optionally also 'leaves', filtering tabid > 99 for user tables). This runs in a fraction of a second, provided statistics are up to date. One poster asked why he needed the figure, but no further discussion followed.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
I could use some help again.
Does anyone know an other (faster) way to determine the number of index-levels
on a table ??
For the moment I use "oncheck -pT dbname", but this takes a very long time and
creates a huge outputfile.
And then I work with unix-tools (grep , awk, ..) on the outputfile.
Thanks for your help.
Jacques
select idxname, tabid, levels
from sysindexes si, systables st
where si.tabid = st.tabid and st.tabname = 'mytable';
If your stats are up-to-date this will be more than good enough.
Art S. Kagel
----- Original Message -----
From: Jacques Lapeire <ids@iiug.org>
At: 8/11 10:20:03
Hi,
I could use some help again.
Does anyone know an other (faster) way to determine the number of index-levels
on a table ??
For the moment I use "oncheck -pT dbname", but this takes a very long time and
creates a huge outputfile.
And then I work with unix-tools (grep , awk, ..) on the outputfile.
Thanks for your help.
Jacques
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Jacques,
My below quick-and-dirty query should run in a fraction of a second.
Kern --
dbaccess $database << EOF 2> /dev/null | grep -v ^$
select tabname[1,18], idxname[1,18], levels
from systables, sysindexes
where systables.tabid=sysindexes.tabid and
systables.tabid > 99 and
tabtype = 'T'
order by 3 desc, 1
EOF
JACQUES LAPEIRE <jacques.lapeire@siemens.com> wrote:
Hi,
I could use some help again.
Does anyone know an other (faster) way to determine the number of index-levels
on a table ??
For the moment I use "oncheck -pT dbname", but this takes a very long time and
creates a huge outputfile.
And then I work with unix-tools (grep , awk, ..) on the outputfile.
Thanks for your help.
Jacques
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Jacques,
I think this query can help you, run it against the dbname which you use.
select tabname, idxname, leaves, levels
from sysindexes i, systables t
where t.tabid = i.tabid
and t.tabid > 99
Celso Coimbra
ClearTech Ltda
"Unlock your revenue"
Tel: (019) 2104-4509
E-mail: ccoimbra@cleartech.com.br
-----Mensagem original-----
De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]Em nome de JACQUES
LAPEIRE
Enviada em: sexta-feira, 11 de agosto de 2006 11:15
Para: ids@iiug.org
Assunto: indexlevels [7258]
Hi,
I could use some help again.
Does anyone know an other (faster) way to determine the number of index-levels
on a table ??
For the moment I use "oncheck -pT dbname", but this takes a very long time and
creates a huge outputfile.
And then I work with unix-tools (grep , awk, ..) on the outputfile.
Thanks for your help.
Jacques
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
JACQUES LAPEIRE wrote: > > Does anyone know an other (faster) way to determine the number of > index-levels > on a table ?? Why do you want to know this? -- Bye now, Obnoxio "... no bill is required as no value was provided." -- Christine Normile
He wants to sell the information to Osama. cheers j. ( j.i.c. ;-) ) -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of Obnoxio The.... Sent: Friday, August 11, 2006 3:50 PM To: ids@iiug.org Subject: Re: RES: indexlevels [7263] JACQUES LAPEIRE wrote: > > Does anyone know an other (faster) way to determine the number of > index-levels > on a table ?? Why do you want to know this? -- Bye now, Obnoxio "... no bill is required as no value was provided." -- Christine Normile **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.