Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, wallacel.

Asked: September 01, 2000 - 8:40 pm UTC

Last updated: October 22, 2021 - 3:49 am UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

Tom:

I have a question regarding index. can you explain me in detail
what the following means.

If the index is a concatenation of multiple columns and one
of the columns contains a NULL value, the row will be in the
index column containing the NULL value and will be left empty.

+++++++++++++++++++++++++++++++++++++++++++++
above is exactly what it says in a book. I can't even understand
it's english."the row will be in the index column containing the NULL value and will be left empty" what does that mean?



and Connor said...

I think what they are trying to say is "NULLs are not stored in b*tree indexes" -- however -- that is not 100% accurate.

It is accurate to say that "if all of the columns in an index entry would be NULL -- that index entry will not be made.". That is -- if an index is built on a column with all NULLS in it -- that index will have zero rows in it. Consider this example:


ops$tkyte@DEV816> create table t ( x int, y int );
Table created.

ops$tkyte@DEV816> insert into t values ( 1, 1 );
1 row created.

ops$tkyte@DEV816> insert into t values ( 1, null );
1 row created.

ops$tkyte@DEV816> insert into t values ( null, 1 );
1 row created.

ops$tkyte@DEV816> insert into t values ( null, null );
1 row created.

ops$tkyte@DEV816> create index t1_idx on t(x);
Index created.

ops$tkyte@DEV816> create index t2_idx on t(y);
Index created.

ops$tkyte@DEV816> create index t3_idx on t(x,y);
Index created.

ops$tkyte@DEV816> analyze index t1_idx validate structure;
Index analyzed.

ops$tkyte@DEV816> select name, lf_rows from index_stats;

NAME LF_ROWS
------------------------------ ----------
T1_IDX 2

there are only two entries in this index since 2 out of 4 entries are entirely NULL -- the entirely NULL entries are skipped

ops$tkyte@DEV816> analyze index t2_idx validate structure;
Index analyzed.

ops$tkyte@DEV816> select name, lf_rows from index_stats;

NAME LF_ROWS
------------------------------ ----------
T2_IDX 2


Same thing here -- 2 out of 4 are NULL, skip them

ops$tkyte@DEV816> analyze index t3_idx validate structure;
Index analyzed.

ops$tkyte@DEV816> select name, lf_rows from index_stats;

NAME LF_ROWS
------------------------------ ----------
T3_IDX 3

Here, only 1 of the index entries is entirely NULL and will not be entered into the index. The 2 partially NULL entries as well as the entirely not NULL entry are made though


Rating

  (75 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Helpful and Informative

Chander, August 28, 2001 - 3:09 am UTC

Thanks Tom .... !

great example (but why VALIDATE STRUCTURE) ?

Alberto Dell'Era, June 26, 2003 - 4:53 pm UTC

Have you used ANALYZE VALIDATE STRUCTURE instead of a plain ANALYZE COMPUTE STATISTICS for any particular reason, or just by chance ?

Tom Kyte
June 26, 2003 - 5:09 pm UTC

I wanted index stats to be populated so I could get the info that it contains.

ops$tkyte@ORA920> create index t_idx on t(a);
Index created.

ops$tkyte@ORA920> analyze index t_idx compute statistics;
Index analyzed.

ops$tkyte@ORA920> select * from index_stats;
no rows selected

ops$tkyte@ORA920> analyze index t_idx validate structure;
Index analyzed.

ops$tkyte@ORA920> select * from index_stats;

    HEIGHT     BLOCKS NAME                           PARTITION_NAME                    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN
---------- ---------- ------------------------------ ------------------------------ ---------- ---------- ----------- ----------
   BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE
---------- ---------- ----------- ---------- ----------- --------------- ------------- ----------------- ----------- ----------
  PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ------------ -------------------- ---------- ------------ -------------- ----------------
         1          8 T_IDX                                                                  2          1          28       7996
         0          0           0          0           0               0             1                 2        7996         28
         1            2                  2.5          0            0              0                0


1 row selected.

 

Ok, but also COMPUTE STATISTICS seems to do the same

Alberto Dell'Era, June 26, 2003 - 5:58 pm UTC

(At least in 9.0.1) index_stats *seems* to be populated the same way using either VALIDATE STRUCTURE or COMPUTE STATISTICS:

SQL> create index xx on t(c);

Index created.

SQL> analyze index xx compute statistics;

Index analyzed.

SQL> select * from index_stats;

         1         16
XX

        20          1         345       3904          0          0           0
         0           0               0             4                17
       3904        345          9            5                    4          0
           0              1               25


SQL> drop index xx;

Index dropped.

SQL>  create index xx on t(c);

Index created.

SQL> analyze index xx validate structure;

Index analyzed.

SQL>  select * from index_stats;

         1         16
XX

        20          1         345       3904          0          0           0
         0           0               0             4                17
       3904        345          9            5                    4          0
           0              1               25

I was just curious to know whether VALIDATE STRUCTURE does something that COMPUTE STATISTICS does not (actually i was thinking about more precise results, since "validate" seems stronger than "statistics"). In this case it seems we get the same output, but VALIDATE (if more precise in general) may be more appropriate for my Oracle-investigating experiments with sqlplus.

BTW Thanks for the answer in real-time ;-)
 

Tom Kyte
June 26, 2003 - 6:18 pm UTC

my example above was a cut and paste from 9203.

Interesting, but I cannot reproduce, the only thing I can think is

a) you created the index
b) validated structure
c) index_stats was populated
d) drop index
e) create index
f) computed statistics
g) index_stats APPEARED full -- but it was really left over from b)

consider

ops$tkyte@ORA9I> create table t as select * from all_users;
create table t as select * from all_users;

Table created.

ops$tkyte@ORA9I> create index t_idx on t(username);
create index t_idx on t(username);

Index created.

ops$tkyte@ORA9I> analyze index t_idx compute statistics;
analyze index t_idx compute statistics;

Index analyzed.

ops$tkyte@ORA9I> select * from index_stats;
select * from index_stats;

no rows selected

ops$tkyte@ORA9I> select * from v$version;
select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.0.1.2.0 - Production
PL/SQL Release 9.0.1.2.0 - Production
CORE    9.0.1.2.0       Production
TNS for Solaris: Version 9.0.1.2.0 - Production
NLSRTL Version 9.0.1.2.0 - Production

ops$tkyte@ORA9I> analyze index t_idx validate structure;
analyze index t_idx validate structure;

Index analyzed.

ops$tkyte@ORA9I> select * from index_stats;
select * from index_stats;

    HEIGHT     BLOCKS NAME                           PARTITION_NAME                    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN
---------- ---------- ------------------------------ ------------------------------ ---------- ---------- ----------- ----------
   BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE
---------- ---------- ----------- ---------- ----------- --------------- ------------- ----------------- ----------- ----------
  PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ------------ -------------------- ---------- ------------ -------------- ----------------
         1         64 T_IDX                                                                 91          1        1664       7996
         0          0           0          0           0               0            91                 1        7996       1664
        21            1                    2          0            0              0                0

 

What happens when we drop the index?

Kamal Kishore, June 26, 2003 - 9:12 pm UTC

Hi Tom,
When we drop the index (after VALIDATE STRUCTURE has been done on it), does the system keep the entry in the INDEX_STATS for that index (even after it is dropped)?

My test run below shows that after dropping the index idx_t the index_stats entry went away but as soon as I re-created the index, the entry in the INDEX_STATS magically re-appeared, even without a VALIDATE STRUCTURE command being issued?

IS this entry still kept hidden somewhere even though the index might have been dropped?
Thanks,

SQL> create table t(x int) ;

Table created.

SQL> create index idx_t on t(x) ;

Index created.

SQL> select * from index_stats ;

no rows selected

SQL> analyze index idx_t validate structure ;

Index analyzed.

SQL> select * from index_stats ;

    HEIGHT     BLOCKS NAME                           PARTITION_NAME                    LF_ROWS
---------- ---------- ------------------------------ ------------------------------ ----------
   LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS
---------- ----------- ---------- ---------- ---------- ----------- ---------- -----------
DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY
--------------- ------------- ----------------- ----------- ---------- ---------- ------------
BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
-------------------- ---------- ------------ -------------- ----------------
         1         16 IDX_T                                                                  0
         1                   3904          0          0           0          0           0
              0             0                 0        3904                                  0
                   1          0            0              0                0


1 row selected.

SQL> drop index idx_t ;

Index dropped.

SQL> select * from index_stats ;

no rows selected

SQL> create index idx_t on t(x) ;

Index created.

SQL> select * from index_stats ;

    HEIGHT     BLOCKS NAME                           PARTITION_NAME                    LF_ROWS
---------- ---------- ------------------------------ ------------------------------ ----------
   LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS
---------- ----------- ---------- ---------- ---------- ----------- ---------- -----------
DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY
--------------- ------------- ----------------- ----------- ---------- ---------- ------------
BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
-------------------- ---------- ------------ -------------- ----------------
         1         16 IDX_T                                                                  0
         1                   3904          0          0           0          0           0
              0             0                 0        3904                                  0
                   1          0            0              0                0


1 row selected.

SQL> disconnect
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
SQL>
 

Tom Kyte
June 27, 2003 - 8:39 am UTC

index stats is just a little "in memory thing" that only has one row and is only visible in your session. You are seeing a "quirk" there.

very strange..

DD, June 26, 2003 - 9:44 pm UTC

Tom,
I tried what Kamal did and surprisingly the stats can be seen not only when the index is recreated but even if it is created on another column but with the same name we see the same stats.. what is the logic behind it..
i m using 9iR2 on RHL 8.0...

Thanks

Tom Kyte
June 27, 2003 - 8:40 am UTC

just a quirk, nothing I would get very excited about. index stats contains one row at a time. It contains the outcome of the last validate structure. it goes away when you exit your session.

analyze .. validate structure online; DOES NOT work ...

Arindom, December 23, 2003 - 9:35 am UTC

Tried analyze ... validate structure online; on 9.2.0.1.0.
Doesn't populate index_stats ; so I guess there's isin't much in Oracle's claim about this enhancement (not taking out a lock on the index )

Index

reader, January 30, 2004 - 8:56 am UTC

Hi Tom,

Is there any way to see reverse key index info?
Any view, function or something else?

Thanks for your help.

Tom Kyte
January 30, 2004 - 9:18 am UTC

ops$tkyte@ORA9IR2> create table t ( x int );
 
Table created.
 
ops$tkyte@ORA9IR2> create index t_idx on t(x) reverse;
 
Index created.
 
ops$tkyte@ORA9IR2> select index_type from user_indexes where
  2  index_name = 'T_IDX';
 
INDEX_TYPE
---------------------------
NORMAL/REV
 

Index

reader, January 30, 2004 - 9:46 am UTC

Thanks for the quick response.

1.Can it be seen by using dump function (besides user_indexes)?

2.This type of indexing is only used for unique indexes when inserts to the table are always in the ascending order of the indexed columns. Why is it so?

Thanks for your precious time and valuable solution.

Tom Kyte
January 30, 2004 - 9:53 am UTC

1) no, not really -- you'd be dumping the column value after it was reversed. you just need to query the data dictionary to see if it is "reversed"

2) that is the most likely use case for it. it helps prevent a really "hot" right hand side of the index by spreading the inserts all over the index structure.

Index

reader, January 30, 2004 - 11:03 am UTC

Thanks for the prompt reply. U r great as usual.

U mean theres no need to colesce the leaf blocks if reverse key indexing is used for unique index?

Thanks.

Tom Kyte
January 30, 2004 - 7:28 pm UTC

wow, massive keyboard failure. it is losing vowels like crazy. That must be driving you crazy I bet. I'd have to buy a new keyboard right away.


No, I was not talking about coalescing or rebuilding at all.

I was describing how the reverse key index could be used to spread the insert active all over the index instead of just on the right hand side - decreasing insert contention on the index (spreading the update load all over the structure instead of just the right hand side of it)

Nice

R.Chacravarthi, March 01, 2004 - 1:00 am UTC

Dear Tom,
Is it possible to cache index data blocks in any part of the
Instance's SGA?
Please do reply.


Tom Kyte
March 01, 2004 - 7:42 am UTC

as long as that part is called "a buffer cache", sure. that is where we cache data blocks, of which indexes are a class of.


they can be in the keep pool, the recycle pool, the default buffer cache or any one of the 4 other sized caches (2k, 4k, 8k, 16k, 32k caches -- one will be default leaving the other 4).

OK

John, March 01, 2004 - 9:17 am UTC

Dear Tom,
Is there a manual way to synching index data with table data?Does oracle does that automagically when the base table
undergoes any modifications?
Please do reply.



Tom Kyte
March 01, 2004 - 9:54 am UTC

index maintainance "just happens" (except for a TEXT index but you didn't say anything about that)

you need do nothing to have indexes maintain themselves.

dbms_stats can do it

Marcio, May 19, 2004 - 10:08 am UTC

Can we do it with dbms_stats?
analyze index t_idx validade structure;

You said the command "analyze" is going to heaven in near future, so this command should be accomplish with dbms_stats -- sorry I couldn't find this in docs.

Tom Kyte
May 19, 2004 - 11:01 am UTC

analyze is going to be "validate" heaven in the future.


analyze is used to validate
dbms_stats to gather stats.

Getting NULL values fast

A reader, August 24, 2004 - 10:22 am UTC

You showed that B*Tree indexes dont store all null values for performance/efficiency reasons, I guess.

But what if my business/data model requires a query of the form

where column is null

and only a handful of rows out of 1000s are null.

How can I make this kind of query go faster?

Thanks

Tom Kyte
August 24, 2004 - 10:43 am UTC

create index on t( decode(column,null,1) );


select * from table where decode(column,null,1) = 1;



How does index store data

San, March 31, 2005 - 3:20 am UTC

Hi Tom

I have the following doubts.
At the first place how does index store data.
What are the contents of index root/ branch block.
The doubt stems from the fact that the leaf blocks contain index entries, so how is an index traversed.
How does the index grow and what are prerequisites for the levels to grow.
When does the index realize that a level has to be incremented.
I am confused regarding index growth. I understand 90/10 & 50/50 splits but what exactly is left sided/ right sided terminology.

There was a question raised by one of my colleagues that an integer datatype column would
be faster searched as compared to any other datatype, for instance against varchar.
He was quoting that integers are stored presorted in core level and strings require multiple bytes,
so while traversing the index, number of blocks traversed should be less.
I tried explaining that datatypes would not effect the performance of indexes.
Does his question have any relevance ?

Would be grateful if you could explain me the doubts and refer any discussion/s where you have covered these in detail.

Rgds
San

Tom Kyte
March 31, 2005 - 7:45 am UTC

concepts guide goes into this.

Expert One on One Oracle goes into this in great detail if you are interested.


In Oracle -- integers are stored in varying length character strings, 2 digit per byte plus sign/exponent and so on. The number format is documented in the concepts guide as well.

The number 1 takes less storage than the number 99999999999999

vsize() can show you this.


If you have a string that represents the natural key of an object (eg: I am building a CODE to DESCRIPTION lookup, or a STATE to REGION lookup or a STRING1 to STRING2 lookup), you should definitely use the string as the natural key (assuming the CODE/STATE/STRING1 is considered unchangeable -- we haven't renamed a state in the US for a long time..)

Otherwise, you have to introduce a surrogate key (extra data in the table) and put numbers in other tables. I'd rather have an employee record with values 'VA', 'CA', 'MD' and so on for the state value, rather than 1, 23, 42. I can many times avoid going to the lookup table alltogether.



create index using STORE IN clause

sns, September 15, 2005 - 7:47 am UTC

I got a wierd error when creating a local index on a pretty big table (1 billion rows).

SQL> create index xie_od_amer_skunum on order_detail_amer(sku_num) nologging parallel (degree 4) local
  2  store in (gps_dat01,gps_ind01,pod_ind02,pod_dat02)
  3  /
create index xie_od_amer_skunum on order_detail_amer(sku_num) nologging parallel (degree 4) local
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P001
ORA-01652: unable to extend temp segment by 512 in tablespace POD_DAT03


I am wondering why Oracle is trying to use POD_DAT03 tablespace when I have not specified in my STORE IN clause?

The database is 8.1.7.

Thanks, 

Tom Kyte
September 15, 2005 - 7:57 am UTC

since you did not specify a tablespace - we are using your default tablespace, or in the case of partitioning - those defaults...

And this temp segment isn't really 'temp' -- all of the extents allocated for your index are marked as temp until the index is created then we convert them into permanent ones

so you are not blowing out on sort space here, you are not having enough room to hold the index itself in that tablespace.

If that is not the tablespace you wanted to use - specify the correct one.

what is STORE IN

sns, September 15, 2005 - 8:06 am UTC

I did mention the tablespace name in my STORE IN clause. Was that the right syntax?

What exactly STORE IN clause does?

Thanks,

Tom Kyte
September 15, 2005 - 8:46 am UTC

can you give us the entire example -- the create table and the create index, it depends on the table structure as well.



DDL,

sns, September 15, 2005 - 9:06 am UTC

I tried submitting the DDL earlier and the web page errored out (may be the length of my text was too big).


I shorted my DDL by removing a lot of partitions and pasting the text. The CREATE INDEX statement is at the bottom.

However, I have list of tablespaces that are part of the table.

GPS_DAT01
POD_DAT01
POD_DAT02
POD_DAT03
POD_DAT04
POD_DAT05
POD_DAT06
POD_DAT07
POD_DAT08
POD_DAT09
POD_DAT10
POD_DAT11
POD_IND03
US_ONLINE_CURR_DAT01


Thanks,

-- Table ORDER_DETAIL_AMER

CREATE TABLE fir.order_detail_amer
(
business_unit_id NUMBER(12),
order_num VARCHAR2(10),
seq_num VARCHAR2(6),
tie_num VARCHAR2(3),
sku_num VARCHAR2(13),
system_class VARCHAR2(5),
option_class VARCHAR2(5),
system_qty NUMBER(10),
order_qty NUMBER(10),
mfg_part_num VARCHAR2(20),
net_price NUMBER(18,3),
net_price_dollars NUMBER(18,3),
net_price_ibu NUMBER(18,3),
unit_cost_at_order NUMBER(18,3),
retail_list_price_at_order NUMBER(18,3),
retail_list_price_dollars NUMBER(18,3),
retail_list_price_ibu NUMBER(18,3),
fpc_cost NUMBER(18,3),
fpc_cost_ibu NUMBER(18,3),
fpc_dollars NUMBER(18,3),
fpc_margin NUMBER(18,3),
future_average_cost NUMBER(15,3),
gross_revenue_dollars NUMBER(15,3),
net_revenue_dollars NUMBER(15,3),
weighted_material_cost NUMBER(15,3),
ship_qty NUMBER(10),
svc_tag VARCHAR2(12),
revenue_type VARCHAR2(2),
base VARCHAR2(7),
order_group VARCHAR2(3),
compensation_cost NUMBER(18,3),
forecasted_service_cost NUMBER(15,3),
system_site VARCHAR2(4),
country_code VARCHAR2(3),
manufact_facility VARCHAR2(10),
item_class VARCHAR2(2),
fiscal_week_num NUMBER(6),
fiscal_month NUMBER(2),
fiscal_quarter NUMBER(2),
fiscal_year NUMBER(4),
extract_date DATE,
system_flag VARCHAR2(1),
collection_site_code VARCHAR2(4),
load_seq_num NUMBER(10,3),
disc_flag VARCHAR2(1),
local_manuf_item_num VARCHAR2(16),
transfer_price NUMBER(15,3),
bundle_option VARCHAR2(7),
bundled_warranty_revenue NUMBER(15,3),
unbundled_warranty_revenue NUMBER(15,3),
warranty_dollars NUMBER(15,3),
base_periph_cost_dollars NUMBER(15,3),
base_periph_rev_disc_dollars NUMBER(15,3),
base_periph_rev_retail_dollars NUMBER(15,3),
box_margin_dollars NUMBER(15,3),
box_cost_dollars NUMBER(15,3),
box_rev_disc_dollars NUMBER(15,3),
box_rev_retail_dollars NUMBER(15,3),
bw_labor_cost_dollars NUMBER(15,3),
bw_labor_rev_disc_dollars NUMBER(15,3),
bw_labor_rev_retail_dollars NUMBER(15,3),
bw_parts_cost_dollars NUMBER(15,3),
bw_parts_rev_disc_dollars NUMBER(15,3),
bw_parts_rev_retail_dollars NUMBER(15,3),
comb_cost_dollars NUMBER(15,3),
comb_margin_dollars NUMBER(15,3),
comb_rev_disc_dollars NUMBER(15,3),
comb_rev_retail_dollars NUMBER(15,3),
consult_cost_dollars NUMBER(15,3),
consult_rev_disc_dollars NUMBER(15,3),
consult_rev_retail_dollars NUMBER(15,3),
del_down_rev_disc_dollars NUMBER(15,3),
del_down_rev_retail_dollars NUMBER(15,3),
dellware_cost_dollars NUMBER(15,3),
dellware_rev_disc_dollars NUMBER(15,3),
dellware_rev_retail_dollars NUMBER(15,3),
dp_prod_cost_dollars NUMBER(15,3),
dp_prod_rev_disc_dollars NUMBER(15,3),
dp_prod_rev_retail_dollars NUMBER(15,3),
dp_svc_cost_dollars NUMBER(15,3),
dp_svc_rev_disc_dollars NUMBER(15,3),
dp_svc_rev_retail_dollars NUMBER(15,3),
init_std_warr_cost_dollars NUMBER(15,3),
init_std_warr_rev_disc_dollars NUMBER(15,3),
init_std_warr_rev_retl_dollars NUMBER(15,3),
install_cost_dollars NUMBER(15,3),
install_rev_disc_dollars NUMBER(15,3),
install_rev_retail_dollars NUMBER(15,3),
non_std_svc_cost_dollars NUMBER(15,3),
non_std_svc_rev_disc_dollars NUMBER(15,3),
non_std_svc_rev_retail_dollars NUMBER(15,3),
non_std_time_cost_dollars NUMBER(15,3),
non_std_time_rev_disc_dollars NUMBER(15,3),
non_std_time_rev_retl_dollars NUMBER(15,3),
non_tied_cost_dollars NUMBER(15,3),
non_tied_margin_dollars NUMBER(15,3),
non_tied_rev_disc_dollars NUMBER(15,3),
non_tied_rev_retail_dollars NUMBER(15,3),
readyware_cost_dollars NUMBER(15,3),
readyware_rev_disc_dollars NUMBER(15,3),
readyware_rev_retail_dollars NUMBER(15,3),
std_warr_svc_cost_dollars NUMBER(15,3),
std_warr_svc_rev_disc_dollars NUMBER(15,3),
std_warr_svc_rev_retl_dollars NUMBER(15,3),
unbun_warr_cost_dollars NUMBER(15,3),
unbun_warr_rev_disc_dollars NUMBER(15,3),
unbun_warr_rev_retail_dollars NUMBER(15,3),
upg_init_warr_cost_dollars NUMBER(15,3),
upg_init_warr_rev_disc_dollars NUMBER(15,3),
upg_init_warr_rev_retl_dollars NUMBER(15,3),
upsell_svc_cost_dollars NUMBER(15,3),
upsell_svc_margin_dollars NUMBER(15,3),
upsell_svc_rev_disc_dollars NUMBER(15,3),
upsell_svc_rev_retail_dollars NUMBER(15,3),
std_value_rule_id VARCHAR2(2),
fiscal_2000_fwd_flag NUMBER(1),
unit_cost_amt NUMBER(18,3),
old_net_revenue_dollars NUMBER(15,3),
old_gross_revenue_dollars NUMBER(15,3),
old_fpc_dollars NUMBER(15,3),
old_warranty_dollars NUMBER(15,3),
old_bundled_warranty_dollars NUMBER(15,3)
)
PARTITION BY RANGE (fiscal_week_num)
(
PARTITION order_detail_amer_9827 VALUES LESS THAN (9828)
PCTFREE 1
PCTUSED 40
INITRANS 6
MAXTRANS 6
TABLESPACE pod_dat05,
PARTITION ORDER_DETAIL_AMER_200601
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_AMER_200602
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_AMER_200603
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_AMER_200604
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_AMER_200605
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_AMER_200606
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_AMER_200607
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_AMER_200609
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_AMER_200610
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_AMER_200617
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_AMER_200618
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_MAX_200619
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_MAX_200620
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_MAX_200621
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_MAX_200622
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_MAX_200623
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_MAX_200624
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_MAX_200625
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_MAX_200626
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_MAX_200627
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_MAX_200628
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_MAX_200629
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_MAX_200630
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_AMER_200631
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_AMER_200632
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_AMER_200633
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01,
PARTITION ORDER_DETAIL_AMER_MAX
PCTFREE 10
INITRANS 6
MAXTRANS 255
TABLESPACE pod_ind01
)
/

-- End of DDL script for ORDER_DETAIL_AMER

create index xie_od_amer_skunum on order_detail_amer(sku_num) nologging
parallel (degree 4) local
store in (gps_dat01,gps_ind01,pod_ind02,pod_dat02)
/



Tom Kyte
September 15, 2005 - 9:48 am UTC

On a hash partitioned table -- store in will distribute the index partitions across all of the listed tablespaces.

On a composite partitioned table (range/hash composite) -- store in will distribute the index partitions across all of the listed tablespaces.

store in is for "hash" partitions and hash subpartitions.

It probably should have raised an error to the effect that the store in wasn't meaningful here.


In 10gr1 and 9ir2 I receive:


ops$tkyte@ORA9IR2> create index t_idx on t(x) nologging parallel(degree 4) local store in (users, tools);
create index t_idx on t(x) nologging parallel(degree 4) local store in (users, tools)
                                                                        *
ERROR at line 1:
ORA-14177: STORE-IN (Tablespace list) can only be specified for a LOCAL index
on a table which is hash or composite partitioned
 
what version have you? 

design this table,

sns, September 15, 2005 - 9:27 am UTC

Well, my previous question is about INDEX creation. I have another question which is probably more related to designing the table.

If you observe the DDL of my ORDERS table, the table is RANGE PARTITIONED on FISCAL_WEEK_NUM attribute. Actually this attribute is not an ideal candidate for partitioning but they chosed it just to distribute the data into multiple tablespaces.

In our daily ETL operations, there are 2-3 million orders that gets into our system. The orders may be new or even 2-3 years older.

Our intention is to insert those 2 million records into the big ORDERS table, but before inserting it should delete the order number if it is already existing.

To achieve this, we do DELETE INSERT everyday (MERGE was even slower).

In this scenario, what do you think the best way to design this table. Heavy deletes on this table is also causing too many free blocks within its segments.

Remember, the ORDER_DETAIL table has 5 indexes and order_num attribute is one among them.

Thanks,


Tom Kyte
September 15, 2005 - 9:52 am UTC

I cannot answer that - if you just wanted to distribute data (2/3 million things isn't very large, not sure that partitioning is actually needed here) you should have hash partitioned, that is what it does. If you have no logical partition key to range or list on - hashing is the thing that does "distribution over many partitions"

just make sure you use 2, 4, 8, 16, 32, 64, 128, ..... partitions (powers of two) if you hash

thanks,

sns, September 15, 2005 - 10:05 am UTC

For your question on CREATE INDEX, the database version is 8.1.7.

About your reply for my second question regarding design,
I think I didn't put my question properly. The ORDER_DETAIL table is 1 billion rows and 2-3 million rows is my INCREMENTAL table. The data in INCREMENTAL table is used to DELETE..INSERT in my base table. The INCREMENTAL table is a non-partitioned table.

So do you think hash partition on my base table (1 billion rows) is a good idea?

How is it going to help us when we do lots of delets and inserts based on ORDER_NUM attribute?

I have drained out all the ideas of designing this table from database point of view. I am just wondering is there any ideas/tricks to design this by thinking OUTSIDE THE BOX(DATABASE).

Thanks,


Tom Kyte
September 15, 2005 - 10:19 am UTC

... So do you think hash partition on my base table (1 billion rows) is a good idea? ....

it is hard to say - for you really need to understand what partitioning does (the physics behind it) and what your goals are (why you want to partition) and how the data is accessed.

If order_num was something with lots of distinct values AND I frequently accessed the data by that key, hashing on it *could* make sense. I would encourage you to simulate and test (if you have Effective Oracle by Design - I talk about partitioning and how it is not fast=true, especially in OLTP, how in OLTP you must be very careful to not negatively impact performance with it)

Index and Null

Peter, September 15, 2005 - 1:04 pm UTC

As you said:
create index on t( decode(column,null,1) );
select * from table where decode(column,null,1) = 1;
will make the query go faster if fewer NULL rows in the table.

1. If I undertand correctly, you are replacing NULL value with 1 What if we already have a row containing 1, need some understanding?

2. if we create an index decode(column,null,1) that will exclude non-NULL rows. in that scenerio, if some query goes to search non-NULL rows will be impacted?

3. Since our data is skewed, can we try with histogram?


Tom Kyte
September 15, 2005 - 1:07 pm UTC

1) we replaced:

NULL with 1
NOT NULL with NULL (so 1 in the table was turned into NULL for the index)

2) you can have more than one index.

3) try what exactly? what is the problem you are trying to solve.

analyze index validate structure

Ailsa, October 03, 2005 - 6:33 am UTC

Hi Tom,

I ubderstand that the online option for validating indexes, doesn't populate index_stats. Is there any other way of calculating how full an index actually is without locking it?
(I've got large indexes in OLTP systems which are 24/7 and I think I've got alot of wasted space as the keys are sequential, and the initial pctfree was set to 70 - I didn't set this value!)

Tom Kyte
October 03, 2005 - 7:39 am UTC

were the tables empty or really small when the index was built - since PCTFREE is only used with an index during the initial index build..... pctfree is not consulted during index maintanance.

Why not do this.... Test your recovery procedures on your backup machine (you do test your backups right? You do regularly test that you can? You have tried it....)

After you test the recovery as you always do, instead of erasing it, start it up and analyze the indexes.....

What is the meaning of LF_ROWS field in index_status P view?

HF Shweta, October 11, 2005 - 6:49 am UTC

What is the meaning of LF_ROWS field in index_status P view?

Tom Kyte
October 11, 2005 - 6:55 am UTC

number of rows in the leaves of the index. may included deleted rows as well - waiting to be reused.

10g index statistics?

Rob H, October 11, 2005 - 12:32 pm UTC

While explaining to a co-worker regarding "analyze table compute statistics" and index stats, I ran into this on a 10g database;
-------------------------------------------
SQL> drop table x;

Table dropped.

SQL> set time on
11:14:59 SQL> set timing on
11:15:02 SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Personal Oracle Database 10g Release 10.1.0.2.0 - Production
PL/SQL Release 10.1.0.2.0 - Production
CORE    10.1.0.2.0      Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

Elapsed: 00:00:00.17
11:15:10 SQL> create table x as select * from all_objects;

Table created.

Elapsed: 00:00:06.10
11:15:26 SQL> select num_rows, avg_space, last_analyzed from user_tables
11:15:44   2  where table_name ='X';

  NUM_ROWS  AVG_SPACE LAST_ANAL
---------- ---------- ---------


Elapsed: 00:00:00.08
11:15:49 SQL> create index x_ind on x(owner);

Index created.

Elapsed: 00:00:01.83
11:16:02 SQL> select index_name, num_rows, blevel, last_analyzed
11:16:24   2  from user_indexes where index_name='X_IND';

INDEX_NAME                       NUM_ROWS     BLEVEL LAST_ANAL
------------------------------ ---------- ---------- ---------
X_IND                               47075          1 11-OCT-05

Elapsed: 00:00:00.10
11:16:33 SQL> select num_rows, avg_space, last_analyzed
11:16:51   2  from user_tables where table_name='X';

  NUM_ROWS  AVG_SPACE LAST_ANAL
---------- ---------- ---------


Elapsed: 00:00:00.07
11:16:59 SQL>
*******************************************



And on a 9i database;
-------------------------------------------
SQL> drop table x;
drop table x
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> set time on
11:20:32 SQL> set timing on
11:20:35 SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

5 rows selected.

Elapsed: 00:00:00.11
11:20:40 SQL> create table x as select * from all_objects;

Table created.

Elapsed: 00:00:02.40
11:21:03 SQL> select num_rows, avg_space, last_analyzed
11:21:35   2  from user_tables where table_name ='X';

  NUM_ROWS  AVG_SPACE LAST_ANAL
---------- ---------- ---------


1 row selected.

Elapsed: 00:00:00.14
11:21:59 SQL> create index x_ind on x(owner);

Index created.

Elapsed: 00:00:01.22
11:22:12 SQL> select index_name, num_rows, blevel, last_analyzed
11:22:27   2  from user_indexes where index_name ='X_IND';

INDEX_NAME                       NUM_ROWS     BLEVEL LAST_ANAL
------------------------------ ---------- ---------- ---------
X_IND

1 row selected.

Elapsed: 00:00:00.05
11:22:39 SQL> analyze table x compute statistics;

Table analyzed.

Elapsed: 00:00:01.09
11:23:17 SQL> select num_rows, avg_space, last_analyzed from user_tables
11:23:34   2  where table_name ='X';

  NUM_ROWS  AVG_SPACE LAST_ANAL
---------- ---------- ---------
      6507       1790 11-OCT-05

1 row selected.

Elapsed: 00:00:00.00
11:23:41 SQL> select index_name, num_rows, blevel, last_analyzed
11:23:48   2  from user_indexes where index_name='X_IND';

INDEX_NAME                       NUM_ROWS     BLEVEL LAST_ANAL
------------------------------ ---------- ---------- ---------
X_IND                                6507          1 11-OCT-05

1 row selected.

Elapsed: 00:00:00.00
11:23:54 SQL>
*******************************************

As you can see on 10g, the index stats are available on index creation and yet, the table does not get any updated statistics?

Any ideas (not sure this is a big deal, but it is different)? 

Tom Kyte
October 11, 2005 - 3:48 pm UTC

interesting, did not realize that. it is different.

the table would get stats dynamically gathered when you hard parse until it "gets stats"

another thing to file away.

Index stats

Jonathan Lewis, October 12, 2005 - 2:49 am UTC

Tom emailed me this one to see if I had come across it.

In fact, lurking somewhere in the documentation, though I can't remember where - possibly New Features - there is a note that "create index" (and possibly some options for rebuild index) automatically do a compute statistics. (This was possible as an option in 9i, but until recently blasted the column stats on the first column of the index.)


Regards
Jonathan Lewis:
Cost Based Oracle now available for pre-order


Stats on Index Only?

Rob h, October 12, 2005 - 11:20 am UTC

I also found this interesting as I'm sure Tom you've used this as an example before showing indexes and NULL columns:

SQL> drop index X_IND;

Index dropped.

SQL> select num_rows from all_tables where table_name='X';

  NUM_ROWS
----------


SQL> update X set owner=null where owner='SYSTEM';

410 rows updated.

SQL> commit;

Commit complete.

SQL> create index X_IND on x(OWNER);

Index created.

SQL> select num_rows from dba_indexes where index_name='X_IND';

  NUM_ROWS
----------
     46665

SQL> analyze table X compute statistics;

Table analyzed.

SQL> select num_rows from dba_tables where table_name='X';

  NUM_ROWS
----------
     47075

SQL> select 47075-46665 from dual;

47075-46665
-----------
        410

Index stats are not generated on NULL columns.

Also, in regards to my first question, index stats are generated on the index during/after the creation (and is it specific to the index segment(?) itself?  I just find it strange that the database would be gathering stats for the index creation and yet, none of it is populated back to the table stats (or am I way off). 

Tom Kyte
October 12, 2005 - 2:15 pm UTC

index stats are not generated on entirely NULL KEYS because btrees don't have entirely null keys in them (if at least ONE of the columns is not null - it will be in the index)

Lurking in the documentation

Mark A. Williams, October 12, 2005 - 2:20 pm UTC

Yes, it is in the documentation for the CREATE INDEX statement:

</code> http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_5010.htm#sthref5118 <code>

ctrl-f for "COMPUTE STATISTICS" and you will find:

COMPUTE STATISTICS In earlier releases, you could use this clause to start or stop the collection of statistics on an index. This clause has been deprecated. ***Oracle Database now automatically collects statistics during index creation and rebuild.*** This clause is supported for backward compatibility and will not cause errors.

*** Emphasis added...

- Mark

Index and PCTFREE

Bipul, November 01, 2005 - 12:04 pm UTC

Hi Tom.

What would be the impact of a low pctfree setting [around 10%] in an OLTP environment? If the index block doesn;t have any space left and an index entry needs to be inserted, then where does it go? For example, there is an index on lname column and it has existing values as Kumar and Kyte. A new row is inserted in the table with lname Kwel. Assuming that there is no space left in the index block for this new entry, then where will it go? Does Oracle moves the existing entry to next block and create space for the new entry i.e. move Kyte to next block and put Kwel in its place ?

What is an appropriate value for PCT_FREE for index in an OLTP environment for tables that gets frequent inserts.

As per Oracle document

"If no more space is available in the appropriate index block, the indexed value is placed where it belongs (based on the lexical set ordering). "

</code> http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10739/indexes.htm#sthref2116 <code>

But its not very clear to me.

I am using Oracle 10.1.0.4.

Thanks
bipul

Tom Kyte
November 02, 2005 - 4:44 am UTC

pctfree only counts on index creation. after the index is created, the pctfree isn't used for an index (since data has a PLACE TO GO in an index, and you don't "udpate" index keys - you delete and insert them someplace else) - it doesn't make sense to reserve space for updates (since updates don't happen)


An index block split is what happens when the block is full and more data needs to go there. If the index block is in the middle (like your example) we do about a 50/50 split - half on one block, half on another - and the process of filling them up begins again. If it is a monotomically increasing attribute you have indexed ( like a sequence, a date of creation, etc) - and we are hitting just the right hand side of the index, we'll do a 90/10 split (90% of the data goes "left" and 10% goes "right" - so the new right hand block is about 90% free for subsequent inserts...


TX - index contention

A reader, December 17, 2005 - 2:55 am UTC

Hi Tom,

Oracle document says

"Waits for TX in mode 4 also occur when a transaction inserting a row in an index has to wait for the end of an index block split being done by another transaction. This type of TX enqueue wait corresponds to the wait event enq: TX - index contention."

I tried to play around this for a while in an attempt to observe this wait event:


APP_USER@10G1> desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
X NUMBER
Y CHAR(20)
Z DATE
Q VARCHAR2(4000)

create index t1_idx on (x, y, z, q);

created 1000 rows on t1, populating x via a growing sequence.

Then on Session 1:
update t1 set x=x ; --to lock all the rows

on Session 2, I tried to populate more rows
via the same sequence, but never see Session 2 waits on TX - index contention.

I also tried to populate T1 with the same value as the row locked by session 1 over and over...cannot see it.

Thanks in advance!

Tom Kyte
December 17, 2005 - 11:14 am UTC

setting x = x will never touch the index on x to modify it. we don't update the index if you don't change the value.

TX - index contention

Jonathan Lewis, December 17, 2005 - 1:17 pm UTC

If you want to try and see this event, you need to make sure you have an index leaf block that is full, and then get two other sessions to simultaneously insert two new rows that both belong in that block.

The first transaction to get in will split the block - and if your timing is accurate enough the second transaction will have to wait for the split to complete. But you may find that you can only make this happen on a system with 2 CPUs. If you have only a single CPU, the transaction that starts the split is likely to stay on the CPU long enough to complete the split.

I have seen this happen - but only on a system with a lot of CPUs and a large number of concurrent transactions on the same object. Worst case was a 45 second jam when two splits of two different leaf blocks propagated up the index and collided several dozen times in a very peculiar way at the branch level.


Thanks a lot to Tom and Jonathan !!!

A reader, December 17, 2005 - 4:08 pm UTC

I can reproduce now:

SYS@10gr1> show parameter cpu

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 8
parallel_threads_per_cpu integer 2


oracle@dblinux:10gr1> grep "TX - index contention" *.trc
10gr1_ora_19676.trc:WAIT #6: nam='enq: TX - index contention' ela= 2 p1=1415053316 p2=196636 p3=55145
10gr1_ora_19676.trc:WAIT #6: nam='enq: TX - index contention' ela= 987 p1=1415053316 p2=196636 p3=55145
10gr1_ora_19676.trc:WAIT #6: nam='enq: TX - index contention' ela= 508 p1=1415053316 p2=196652 p3=55192
10gr1_ora_19676.trc:WAIT #6: nam='enq: TX - index contention' ela= 645 p1=1415053316 p2=196609 p3=55253
10gr1_ora_19676.trc:WAIT #6: nam='enq: TX - index contention' ela= 3 p1=1415053316 p2=196613 p3=55314
10gr1_ora_19676.trc:WAIT #6: nam='enq: TX - index contention' ela= 770 p1=1415053316 p2=196613 p3=55314
10gr1_ora_19676.trc:WAIT #6: nam='enq: TX - index contention' ela= 642 p1=1415053316 p2=196642 p3=55320
10gr1_ora_19676.trc:WAIT #6: nam='enq: TX - index contention' ela= 904 p1=1415053316 p2=196621 p3=55373
10gr1_ora_19676.trc:WAIT #6: nam='enq: TX - index contention' ela= 10047 p1=1415053316 p2=196651 p3=55384
10gr1_ora_19676.trc:WAIT #6: nam='enq: TX - index contention' ela= 780 p1=1415053316 p2=196625 p3=55461
10gr1_ora_19676.trc:WAIT #6: nam='enq: TX - index contention' ela= 598 p1=1415053316 p2=196653 p3=55475
10gr1_ora_19676.trc:WAIT #6: nam='enq: TX - index contention' ela= 288 p1=1415053316 p2=196643 p3=55509
10gr1_ora_19676.trc:WAIT #6: nam='enq: TX - index contention' ela= 251 p1=1415053316 p2=196624 p3=55515
10gr1_ora_19676.trc:WAIT #6: nam='enq: TX - index contention' ela= 180 p1=1415053316 p2=196613 p3=55539
10gr1_ora_20782.trc:WAIT #2: nam='enq: TX - index contention' ela= 705 p1=1415053316 p2=196634 p3=55094
10gr1_ora_20782.trc:WAIT #2: nam='enq: TX - index contention' ela= 241725 p1=1415053316 p2=196619 p3=55102

......


Here is what I did:

APP_USER@10gr1> desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
X NUMBER
Y CHAR(20)
Z DATE
Q VARCHAR2(4000)


APP_USER@10gr1> select dbms_metadata.get_ddl('INDEX','T1_IDX4') from dual;

DBMS_METADATA.GET_DDL('INDEX','T1_IDX4')
--------------------------------------------------------------------------------

CREATE INDEX "APP_USER"."T1_IDX4" ON "APP_USER"."T1" ("Q", "Z")
PCTFREE 80 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS....




then launch two sessions, both do the following:

1 begin
2 for x in 1..10000 loop
3 insert into t1 values(1162,'1060000abcdefg', sysdate, rpad('x',2000,'x'));
4 end loop;
5* end;




1 select chr(bitand(1415053316, -16777216)/16777215)||
2 chr(bitand(1415053316, 16711680)/65535) type,
3* mod(1415053316,16) "mode" from dual
app_user@10gr1 /



TY mode

-- ----------

TX 4



I will use this to manually create a deadlock situation to see if I can reproduce what happened in my production. It seems to me that this specific wait event can be part of deadlock as well.



Global and local Index

marc, February 02, 2006 - 3:07 pm UTC

Hi Tom,

I'm interessted to know more about Local and global Indexes!
Please let me know if I am right with me questions

1.)
I mean that Local Index on a partioned table are always asigned to one partion.
I can't add a partion when no partion to tables is added.

2.)
With global index I must add an maxvalue partion to index.

3.)
When the index can be used the index debars the partions that the result of thequery not affect.

4.)
When can I use a local or global index on a standard table or better when should i prefere local or global index?

Thanks
marc

Tom Kyte
February 03, 2006 - 1:29 pm UTC

local indexes - there is a 1 to 1 parity between index partitions and table partitions. If you look at any single index partition, it only points to rows in a single table partition - never across table partitions.


global indexes, the index is partitioned using it's own partitioning scheme. Each index partition may point to as many table partitions as it likes. There is no relationship between index partitions and table partitions.


If you would like a "long article" on this - the concepts guide has some, the data warehouse guide has some and my book expert Oracle: database architecture has some as well. A discussion of "when you would prefer one over the other" takes quite a few pages to do correctly.

enq: TX - index contention

Thiru, September 07, 2006 - 3:59 pm UTC

Based on the comments of Jonathan Lewis reproduced below :

"TX - index contention  December 17, 2005 
Reviewer:  Jonathan Lewis  from UK 

If you want to try and see this event, you need to make sure you have an index leaf block that is full, and then get two other sessions to simultaneously insert two new rows that both belong in that block. 

The first transaction to get in will split the block - and if your timing is accurate enough the second transaction will have to wait for the split to complete. But you may find that you can only make this happen on a system with 2 
CPUs. If you have only a single CPU, the transaction that starts the split is likely to stay on the CPU long enough to complete the split"

------------------
I am facing a similar issue with regard to TX-index contention. Though the time spent on the event is very negligible, this event shows up quite often during the running.

These are some of the facts:
SQL> show parameter cpu

NAME                                 TYPE        VALUE
cpu_count                            integer     6
parallel_threads_per_cpu             integer     2


I had a procedure that was being run in multiple sessions and each session's data not overlapping into another session though the objects accessed were the same. When the procedures were running, querying the v$session gave the following information:

select v.sid,v.SERIAL#,v.EVENT,v.BLOCKING_SESSION,v.BLOCKING_SESSION_STATUS  from v$session v where username ='DBUSER' and  status='ACTIVE'

273    250    buffer busy waits        NOT IN WAIT
274    62    buffer busy waits        NOT IN WAIT
277    62    latch: cache buffers chains        NOT IN WAIT
282    25    buffer busy waits        NOT IN WAIT
293    59    buffer busy waits        NOT IN WAIT
297    59    read by other session        UNKNOWN
299    156    buffer busy waits        NOT IN WAIT
311    148    buffer busy waits        NOT IN WAIT

The other events shown when refreshing the above:

273    250    buffer busy waits        NOT IN WAIT
274    62    buffer busy waits        NOT IN WAIT
277    62    buffer busy waits        NOT IN WAIT
282    25    buffer deadlock        UNKNOWN
293    59    enq: TX - index contention    297    VALID
297    59    buffer busy waits        NO HOLDER
299    156    buffer busy waits        NOT IN WAIT
311    148    buffer busy waits        NOT IN WAIT

and on one more refresh:
273    250    direct path read temp        UNKNOWN
274    62    log buffer space        UNKNOWN
277    62    log buffer space        UNKNOWN
282    25    log buffer space        UNKNOWN
293    59    log buffer space        UNKNOWN
297    59    log buffer space        UNKNOWN
299    156    log buffer space        UNKNOWN
311    148    log buffer space        UNKNOWN

a. With the above information, can you please comment on possible bottlenecks down the road. The data that was accessed was around 6 mill  and the actual data to be used would be around 50mill.

b.Should I be doing a trace on the whole process to find out more? If so, what is a good way of doing it other than inserting the 'alter session ...trace events' in the code.

c.Will these events be a performance hinderance? 

Some more information about the db:

sho sga

SQL> sho sga

Total System Global Area 2147483648 bytes
Fixed Size                  2022248 bytes
Variable Size             486540440 bytes
Database Buffers         1644167168 bytes
Redo Buffers               14753792 bytes

parameter pga and sga:

pga_aggregate_target                 big integer 250M
sga_max_size                         big integer 2G
sga_target                           big integer 2G

The process completes successfully within reasonable time though the aim is to improve.

Thanks so much.
 

Tom Kyte
September 08, 2006 - 4:05 pm UTC

... Though the time
spent on the event is very negligible, this event shows up quite often during
the running.....

this makes me say "so if the time is negligible overall, why do you care?"

If I do something 500 times, but it takes 0.01 seconds - so what?
If I do something 5 times and it takes 10 seconds - then I might care

one thing I don't see here is "how much time" you have spent on these things. that is the only real "consideration"

What´s better?

Eduardo, September 29, 2006 - 7:46 am UTC

Hi Tom, I want to ask you a simple question. ¿ What´s better ? Placing the index and data on a different tablespace o both in the same tablespace.

Regards.

Tom Kyte
September 29, 2006 - 8:38 am UTC

how long is a piece of string?

use tablespaces to make your life BETTER and more CAREFREE.

that is all, are you happier if they are in the same or separate tablespaces?

Should I create Index in such scenario ?

MJani, October 10, 2006 - 3:00 am UTC

Hi TOM ,
Always thankfull to you by all your efforts !
Now I have one question regarding to create Index or not in this situation !
1) I am having one table " temp_promo_calc0_0 " which contains 27,00,000 to 30,00,000 rows . This table is intermediate table and used only once as in below query .

SELECT ctry_code,
co_code,
cust_nbr,
item_id,
pkg_nbr,
event_nbr,
base_sdv_amt,
event_allow_amt,
event_allow_pct,
ROW_NUMBER
() OVER (PARTITION BY ctry_code, co_code, cust_nbr, item_id, pkg_nbr ORDER BY pkg_nbr,
promo_seq_nbr)
rn
FROM temp_promo_calc0_0
WHERE BASE_SDV_AMT<>0

Now what you suggest should I go for benchmarking with different solution to performance or directlly create index on it .
One more point this table will be daily trucated and filledup during the process . Thus index may create overhead !

With Regards


Tom Kyte
October 10, 2006 - 7:43 am UTC

it is highly doubtful that an index would be useful as I would assume MOST rows from the table will be returned.

Is it possible to disable stats generation while creating indexes in 10G

A reader, October 18, 2006 - 9:22 pm UTC

Is it possible to disable automatic stats generation while creating indexes in 10G.

Tom Kyte
October 19, 2006 - 8:02 am UTC

no, why would you want to?

index size

A reader, October 24, 2006 - 3:35 pm UTC

I want to create a single query that returns owner, table name, table size, index size and index count in that table


Tom Kyte
October 25, 2006 - 9:17 am UTC

go ahead?

(not really sure what "index_count" is or means...)

but you can certainly do that. you have user_indexes which tells you number of keys in the index, number of blocks in the index

you have user_tables which has the information about the tables.

assuming statistics are up to date enough - just join.

To: A reader

Michel Cadot, October 25, 2006 - 10:17 am UTC

You can find all these data in dba_segments.
The query seems quite easy

SQL> desc dba_segments
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 OWNER                                     VARCHAR2(30)
 SEGMENT_NAME                              VARCHAR2(81 CHAR)
 PARTITION_NAME                            VARCHAR2(30)
 SEGMENT_TYPE                              VARCHAR2(18 CHAR)
 TABLESPACE_NAME                           VARCHAR2(30)
 HEADER_FILE                               NUMBER
 HEADER_BLOCK                              NUMBER
 BYTES                                     NUMBER
 BLOCKS                                    NUMBER
...

Michel
 

Indexes on nulls

Sanji, November 07, 2006 - 4:28 pm UTC

Hello Tom,

Is there a better methodology whereby the following logic can be improvized.

The Table "T" has around half a million records.

UPDATE T SET Col1 = :b1 WHERE Col1 is null;
UPDATE T SET Col2 = :b1 WHERE Col2 is null;
.
.
.
.
UPDATE T SET Col13 = :b1 WHERE Col13 is null;

I created a function based index and modified the where clause in the updates.

create index t_fidx on t(decode(col1,null,1));
create index t_fidx on t(decode(col12,null,1));
.
.
create index t_fidx on t(decode(col13,null,1));

Though, the indexes have tremendously accelerated the response time, but i feel this is not an appropriate logic. Anything apart from "DEFAULT" for the columns ?

Thanks
Sanji

Tom Kyte
November 08, 2006 - 8:14 am UTC

why not NO INDEXES and a single pass?

update t set col1 = decode( col1, null, col1, :b1 ),
col2 = decode( col2, null, col2, :b1 ),
...

actually, in looking at this, it looks like you are trying to "not use nulls" and will be sticking in some REALLY BAD invalid value in this field?

is that true? If so, just stop, come in off of the ledge, lets talk about what you are going to do and why you think you should do it???


and what is the comment about the DEFAULT clause?

Indexes on nulls

Sanji, November 08, 2006 - 1:44 pm UTC

Tom,
The application design and logic is alien to me as i am new to this project. Would take a while before i can figure out.
The comment regarding the DEFAULT clause was for using DEFAULT values against null values for the columns.

Regards
Sanji

Tom Kyte
November 08, 2006 - 8:03 pm UTC

and how would that work (default)

show us what you meant, it is not clear.

Default Column Values

Sanji, November 10, 2006 - 12:21 pm UTC

What i meant, was (though should have prepared a test case) that default values at the time of table creation would substitute nulls with default values during subsequent inserts and then these columns values can be indexed. The update clause too needs to be changed as you rightly quoted.

Perhaps that might perform as good as decode( col1, null, col1, :b1 ) .......

I reiterate, i should have a testcase to support it. Would certainly try producing one.

Regards
Sanji

Tom Kyte
November 10, 2006 - 2:54 pm UTC

null columns can be "indexed"

</code> http://asktom.oracle.com/Misc/something-about-nothing.html <code>

I'm telling you - don't stuff "fake values" in place of NULLs - you don't need to and it would be a bad idea.

Index

Murali, November 13, 2006 - 9:55 am UTC

Tom:

I have 2 table  po_policy, pp_per_pol 

po_policy table have index on po_pol_no column   
pp_per_pol  table  have index on pp_pol_no .  
and index and tables analyzed .Both columns number datatype.

But i am bit surprised , when i do equi join  query not using index  
but when i pass values its using index .
Can you please  help to  Clarify the cause when  i do equi join why its not using index   ??


  1  SELECT  * FROM PO_POLICY AB ,PP_PER_POL  WHERE PO_POL_NO= 1
  2*                                              AND PP_pOL_NO=1
SQL> /

Execution Plan
----------------------------------------------------------

-----------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |   413 |     7 |
|   1 |  NESTED LOOPS                |              |     1 |   413 |     7 |
|   2 |   TABLE ACCESS BY INDEX ROWID| PO_POLICY    |     1 |   254 |     3 |
|   3 |    INDEX UNIQUE SCAN         | PK_PO_POLNO  |     1 |       |     2 |
|   4 |   TABLE ACCESS BY INDEX ROWID| PP_PER_POL   |     1 |   159 |     4 |
|   5 |    INDEX RANGE SCAN          | IK_PP_POL_NO |     1 |       |     2 |
-----------------------------------------------------------------------------

Note


 1* SELECT  * FROM PO_POLICY AB ,PP_PER_POL  WHERE PO_POL_NO= PP_pOL_NO
 2  /

xecution Plan
---------------------------------------------------------

----------------------------------------------------------------
 Id  | Operation          | Name       | Rows  | Bytes | Cost  |
----------------------------------------------------------------
   0 | SELECT STATEMENT   |            |  1322K|   520M| 42995 |
   1 |  HASH JOIN         |            |  1322K|   520M| 42995 |
   2 |   TABLE ACCESS FULL| PP_PER_POL |  1322K|   200M|  6580 |
   3 |   TABLE ACCESS FULL| PO_POLICY  |  1284K|   311M|  9442 |
----------------------------------------------------------------
 

Tom Kyte
November 14, 2006 - 4:02 am UTC

THANK GOODNESS it does not use any indexes, that would be horrible.

Good thing you are using the CBO, RBO would do the wrong thing.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6749454952894#6760861174154 <code>

Index block split

Sven, November 25, 2006 - 6:35 pm UTC

Hi Tom,

AFAIK, the index block is split when the index block is full.
I would like to know if there is any other 'special' situation which cause index block to split?
I was searching 'all over' but could not find any info.
If yes, how this can be recognized i.e. wait event or similar?

Thanks indeed,
Sven


Tom Kyte
November 25, 2006 - 6:48 pm UTC

well, technically, I guess during a "build" of the index - it'll obey the pctfree and "split" before it fills - but that isn't really a "true split"

Thanks as always!

Sven, November 26, 2006 - 12:19 pm UTC


multiple updates against single update

Sanji, November 28, 2006 - 5:14 pm UTC

Tom
Ref:Indexes on nulls November 07, 2006 Reviewer: Sanji from Shelton, Connecticut

Following is a piece of code that I wrote after the post.

UPDATE XOCUBEACC_TREE SET
Account2=DECODE(ACCOUNT2,NULL,ACCOUNT1,Account2),
Account2_Text=DECODE(ACCOUNT2,NULL,ACCOUNT1_TEXT,Account2_Text),
Account3=DECODE(ACCOUNT3,NULL,ACCOUNT2,Account3),
Account3_Text= DECODE(ACCOUNT3,NULL,ACCOUNT2_Text,ACCOUNT3_TEXT),
Account4=DECODE(ACCOUNT4,NULL,ACCOUNT3,ACCOUNT4),
Account4_Text=DECODE(ACCOUNT4,NULL,ACCOUNT3_TEXT,ACCOUNT4_Text),
Account5=DECODE(ACCOUNT5,NULL,ACCOUNT4,ACCOUNT5),
Account5_Text=DECODE(ACCOUNT5,NULL,ACCOUNT4_TEXT,ACCOUNT5_Text),
Account6=DECODE(Account6,NULL,ACCOUNT5,ACCOUNT6),
Account6_Text=DECODE(ACCOUNT6,NULL,ACCOUNT5_TEXT,ACCOUNT6_Text);

The result of
select count(*) from xocubeacc_tree where account2 is null = 0
select count(*) from xocubeacc_tree where account3 is null = 0
select count(*) from xocubeacc_tree where account4 is null != 0
.
.
If i change the query to
UPDATE XOCUBEACC_TREE SET
Account2=DECODE(ACCOUNT2,NULL,ACCOUNT1,Account2),
Account2_Text=DECODE(ACCOUNT2,NULL,ACCOUNT1_TEXT,Account2_Text);

UPDATE XOCUBEACC_TREE SET
Account3=DECODE(ACCOUNT3,NULL,ACCOUNT2,Account3),
Account3_Text= DECODE(ACCOUNT3,NULL,ACCOUNT2_Text,ACCOUNT3_TEXT);
UPDATE XOCUBEACC_TREE SET

Account4=DECODE(ACCOUNT4,NULL,ACCOUNT3,ACCOUNT4),
Account4_Text=DECODE(ACCOUNT4,NULL,ACCOUNT3_TEXT,ACCOUNT4_Text);
.
.
The the results of
select count(*) from xocubeacc_tree where account2, account3.... is null = 0

1>Why would the 2 update statements produce different results ?
2> One of the update statements, Update xocubeacc_tree set account9=decode(account9,null,account8,account9), stalls. When i trace the session, i see loads of db file sequential waits. This continues to hang if i do not commit in between. Quite understandably, the server has to construct read consistent copies of the blocks, BUT what is confusing is the relationship between logical I/O and sequential reads.
Is the server process trying to construct a read consistent image and simultaneously waiting on I/O ?

Thanks
Sanji

Tom Kyte
November 28, 2006 - 8:06 pm UTC

sorry, did not follow this at all...

Updates

Sanji, November 29, 2006 - 9:30 am UTC

Tom

The following code updates the table to set column values
account2, account3, account4....., account12 to not null values.

UPDATE XOCUBEACC_TREE SET
Account2=DECODE(ACCOUNT2,NULL,ACCOUNT1,Account2),
Account3=DECODE(ACCOUNT3,NULL,ACCOUNT2,Account3),
Account4=DECODE(ACCOUNT4,NULL,ACCOUNT3,ACCOUNT4),

When we check the null count for columns account4, account5...,account12, there still are null values.

If we issue separate update statements for account columns as in
UPDATE XOCUBEACC_TREE SET
Account2=DECODE(ACCOUNT2,NULL,ACCOUNT1,Account2);

UPDATE XOCUBEACC_TREE SET
Account3=DECODE(ACCOUNT3,NULL,ACCOUNT2,Account3);
.
.
UPDATE XOCUBEACC_TREE SET
Account12=DECODE(ACCOUNT12,NULL,ACCOUNT,ACCOUNT12);

then the subsequent account column values are all not null (which is precisely what we want)

The two update statements produce different result sets.

Wanted to cofirm if we are doing something wrong.

Thanks
Sanji

Tom Kyte
November 30, 2006 - 9:04 am UTC

that does not set them to not null values.


not at all. why do you believe that?


in your update:

UPDATE XOCUBEACC_TREE SET
Account2=DECODE(ACCOUNT2,NULL,ACCOUNT1,Account2),
Account3=DECODE(ACCOUNT3,NULL,ACCOUNT2,Account3),
Account4=DECODE(ACCOUNT4,NULL,ACCOUNT3,ACCOUNT4),


if account1 WAS null and account2 WAS null - then account2 will obviously STILL BE NULL.


tell us in "specification" form what you are trying to achieve - it sounds like the COALESCE function is what you are looking for.

CHAR vs VARCHAR2

Sanji, November 29, 2006 - 4:11 pm UTC

Tom, further to the previous question of the update statements producing different result sets, i have another question.
Columns ACCOUNT1 to ACCOUNT12 are CHAR(150) datatypes.

If i update columns (as mentioned in the previous post) individually, each update consumes almost 3-7 minutes, except for ACCOUNT9 update, which consumed over 2 hours. There are 250,000 records in the table.

I tried recreating the table with ACCOUNT1-ACCOUNT12 columns as VARCHAR2(15).
The same updates were now happening in matter of seconds.

CHAR(150) vs VARCHAR2(15). Why such a difference. The only reason i could think of was that when the account fields were created as CHAR(150), each field is 150 bytes with blanks padded to the end of the string. Updating each row and then padding blanks is what might be taking time.

Thanks
Sanji

Tom Kyte
November 30, 2006 - 9:35 am UTC

ouch - this entire thing hurts my head so bad....


a really bad data model (account1, account2, ... - yuck)

a char(150) - ouch, why why why.


and the entire thought of the update - why not JUST FETCH WHAT YOU WANT, eg:

select ...
coalesce( acct1, acct2, acct3, .... )


whatever, maybe this is just not worth doing

I gave a stupid reason

Sanji, November 29, 2006 - 4:25 pm UTC

That was a stupid reason i gave for the updates taking long on a CHAR(150) field against VARCHAR2(15).
I created 2 tables, TEST1 with CHAR(150) datatype fields and TEST2 with VARCHAR2(15) datatype fields. Following are the stats.
SANJI@FDCTL>select owner, segment_name, bytes,blocks,extents from dba_segments where segment_name in ('TEST1','TEST2');
OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS
---------- -------------------- ---------- ---------- ----------
SANJI TEST1 243269632 29696 100
SANJI TEST2 15728640 1920 30

TEST1 is 15 times TEST2. Obviously the update process needs to visit and access more number of blocks.

Sorry about the previous post.
Sanji

UPDATES

Sanji, November 30, 2006 - 9:40 am UTC

Tom,
What we are trying to achieve is that all ACCOUNT columns should be not null.
Account1 is not null (by default)
Account2 =DECODE(ACCOUNT2,NULL,ACCOUNT1,Account2)
.
.
Account12=DECODE(ACCOUNT12,NULL,ACCOUNT11,Account12)

Wanted to achieve this through a single update.
Thanks
Sanji

Tom Kyte
November 30, 2006 - 10:06 am UTC

ok, I'll be snarky here :)


You wrote:
What we are trying to achieve is that all ACCOUNT columns should be not null.

I'll answer:
udpate t set account1 = 'x', account2 = 'x', ....


there you go, they are all not null now.


Now, I presume you actually have some logic you would like to actually use. Providing us with SQL THAT DOES NOT WORK is useless to explain to us what that logic is.


You see - you are misunderstanding how your sql update works (apparently), and we are not having that same misunderstanding (I know what that update should do, and it is - and it definitely would NOT set all columns to a not null value) - therefore we cannot "guess" what your logic actually is.

Updates

Sanji, November 30, 2006 - 10:33 am UTC

Oops... i didn't convey the logic appropriately.

1>Create table XOCUBEACC_TREE using a SELECT clause. There are generally 250,000+ rows in this table.
2>For all rows
Account1 is (always) not null.
Account2 if null should be set to account1
Account3 if null should be set to account2
Account4 if null should be set to account3
.
.
Account12 if null should be set to account11.

Eventually, the account columns are populated with actual values, either through the select clause or with the values of an account level prior to them.

Sanji

Tom Kyte
November 30, 2006 - 10:49 am UTC

I sort of think the logic isn't what you say.

Preciseness and clarity are important.

I believe you are saying:

a) account1 is always not null

b) start by setting account2 = account1 if account2 is null

c) then, AFTER THAT ASSIGNMENT, do to account3 and 2 what you just did to account1 and 2

d) and so on


I tried to give you a hint with coalesce, so here is a strong hint:

ops$tkyte%ORA10GR2> create table t ( a1 int not null, a2 int, a3 int, a4 int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t
  2  select rownum,
  3         case when dbms_random.value(0,1.0001) < .25 then user_id end,
  4         case when dbms_random.value(0,1.0002) < .25 then user_id end,
  5         case when dbms_random.value(0,1.0003) < .25 then user_id end
  6    from all_users
  7   where rownum <= 10
  8  /

10 rows created.

ops$tkyte%ORA10GR2> select * from t;

        A1         A2         A3         A4
---------- ---------- ---------- ----------
         1
         2         64
         3         63
         4         94
         5         60
         6
         7
         8                    57
         9                               56
        10

10 rows selected.

ops$tkyte%ORA10GR2> update t
  2  set a2 = coalesce(a2,a1),
  3      a3 = coalesce(a3,a2,a1),
  4      a4 = coalesce(a4,a3,a2,a1)
  5  /

10 rows updated.

ops$tkyte%ORA10GR2> select * from t;

        A1         A2         A3         A4
---------- ---------- ---------- ----------
         1          1          1          1
         2         64         64         64
         3         63         63         63
         4         94         94         94
         5         60         60         60
         6          6          6          6
         7          7          7          7
         8          8         57         57
         9          9          9         56
        10         10         10         10

10 rows selected.
 

Thanks

A reader, November 30, 2006 - 11:14 am UTC


COALESCE Wonderful feature

Sanji, November 30, 2006 - 3:40 pm UTC

Tom,
This is how the data is being loaded in the table XOCUBEACC_TREE
SELECT DISTINCT d1.cubeid,
LTRIM( RTRIM(d1.parent )) AS Account1,
LTRIM( RTRIM(d2.joincol )) AS Account2,
LTRIM( RTRIM(d3.joincol )) AS Account3
.
.
LTRIM( RTRIM(d12.joincol )) AS Account12
FROM (SELECT DISTINCT cubeid, parent, child, joincol, name
FROM xocubeacc
WHERE SUBSTR(parent, 1, 1 ) <> '#'
AND INSTR(parent, ':') = 0
) d1
left join
(SELECT DISTINCT cubeid, parent, name
FROM xocubeacc
WHERE SUBSTR(parent, 1, 1 ) <> '#' AND INSTR(parent, ':') = 0
AND parent = child
) dt
ON (d1.parent = dt.parent AND dt.cubeid = d1. cubeid)
left join
(SELECT DISTINCT cubeid, parent, child, joincol, name
FROM xocubeacc
WHERE SUBSTR(parent, 1, 1 ) <> '#'
) d2
ON (d2.parent = d1.child AND d2.cubeid = d1.cubeid)
left join
(SELECT DISTINCT cubeid, parent, child, joincol, name
FROM xocubeacc
WHERE SUBSTR(parent, 1, 1 ) <> '#'
) d3
.d4
.
.
.d12
WHERE d1.parent <> d2.child;

Table XOCUBEACC has the following structure/ sample records

CUBEID PARENT CHILD JOINCOL SORTORD NAME
------ ---------- -------------------- ------------- ------- ---------
URI 16000999 16000174:16000999 16000174 990 EQUIP 1
URI 16000999 16000175:16000999 16000175 991 EQUIP 2
URI 16000999 16000176:16000999 16000176 992 EQUIP 3
URI 16000999 16000177:16000999 16000177 993 EQUIP 4
URI 16000999 16000178:16000999 16000178 994 EQUIP 5
URI 16000999 16000179:16000999 16000179 995 EQUIP 6
URI 16000999 16000180:16000999 16000180 996 EQUIP 7
URI 16000999 16000181:16000999 16000181 997 EQUIP 8
URI 16000999 16000182:16000999 16000182 998 EQUIP 9

I realized that this can be achieved through a hierarchial query, but lost my way somehow.

This is what i was trying to code

select distinct parent, rpad( '*', (level-1)*2, '*' ) || joincol JOINEDCOL,child, cubeid
from xocubeacc
start with parent in (select parent from xocubeacc where SUBSTR(parent, 1, 1 ) <> '#' AND INSTR(parent, ':') = 0)
connect by prior child=parent
/
How do i incorporate the condition "WHERE d1.parent <> d2.child " as mentioned in the original query.
Secondly, is the approach correct ?

Thanks
Sanji

Tom Kyte
November 30, 2006 - 3:46 pm UTC

i don't know - there is really not sufficient data to comment

and you'll have to tell me what this has to do with index, stay on track here - please.

(asking a well formed question takes time and much energy on your part - you have to really be willing to be precise, to explain stuff - I don't know how you expect someone to look at SOMETHING THAT DOES NOT WORK - and simply say "it should work but doesn't - can you make it work")

Continuity

Sanji, November 30, 2006 - 3:56 pm UTC

I apologize for the continuity and irrelevance. I'd post the question with more details in the proper forum.
Sanji

why does it use index??

nmgzw, November 30, 2006 - 9:17 pm UTC

I have a sql,you say "null" doesn't exists in index.So i think my sql coundn't use index,but it uses it.

Counld you help me explain it,thanks in advance.

TABLE: MEREQUESTITEM
SQL> desc merequestitem
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                    NOT NULL NUMBER(10)
 NUMBERS                                                        VARCHAR2(30)

INDEX: MEREQUESTITEM_NUMID(numbers,id)

INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ -----------------------------
MEREQUESTITEM_NUMID            NUMBERS                                      1
MEREQUESTITEM_NUMID            ID                                           2

**************
SQL> select * from merequestitem where numbers is null;

no rows selected

Elapsed: 00:00:00.53

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=272)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'MEREQUESTITEM' (Cost=2 C
          ard=1 Bytes=272)

   2    1     INDEX (RANGE SCAN) OF 'MEREQUESTITEM_NUMID' (NON-UNIQUE)
           (Cost=3 Card=1)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          3  physical reads
          0  redo size
       6236  bytes sent via SQL*Net to client
        460  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


SQL> select /*+ no_index(merequestitem MEREQUESTITEM_NUMID)*/ * from merequestitem where numbers is null;
select /*+ no_index(merequestitem MEREQUESTITEM_NUMID)*/ * from merequestitem where numbers is null
                                                                *
ERROR at line 1:
ORA-01013: user requested cancel of current operation


Elapsed: 00:00:08.03
**************************
 

Tom Kyte
December 01, 2006 - 5:16 am UTC

that is NOT what I said at all - in fact, my original answer above was to DEMONSTRATE that nulls may in fact be in the index.

</code> http://asktom.oracle.com/Misc/something-about-nothing.html http://asktom.oracle.com/Misc/mull-about-null.html <code>

your view

HK, December 15, 2006 - 11:13 am UTC

Tom,

I have often seen people blindly adding indexes on tables thinking that it will help improve the query performance. For example:

Create Table Employee (emp_id number primary key, emp_name varchar2(64), ssn varchar2(32), postcode varchar2(32), phone varchar2(32),.....);

create unique index ix1employee on employee(ssn);

Assume this table has 1 million rows.

Scenario here is, there are many applications that accesses this table mostly via employee id or ssn. However there is one application that sometime accesses this table via column "phone", so is it worth adding an index on this column just to satisfy a phenomenon which occurs rarely?

Your views will be much appreciated.

Thanks in advance.

Regards,

HK.

Tom Kyte
December 15, 2006 - 11:46 am UTC

there can only be one answer here

it depends


if the person accessing via the "phone" attribute has no stated response time requirements AND the table is modified frequently (lots of rows added, phone column updated a lot) - then maybe no index is OK.

if not, maybe the index is required.



Concatinated index

Balu, January 02, 2007 - 1:49 am UTC

Dear Tom,

I have small question on index, as i am aware in oracle 8i we dont have concept called skip scan index and we are running our oracle application's 11.0.3 on 8.1.7 database version . i have seen in many tables there is a concatinated index , if we dont have the concept of skip scan index on 8i then what is the use of creating concatinated index can you pls help out if possible with examples.

Regards

Balu.
Tom Kyte
January 02, 2007 - 8:04 am UTC

create index i on t(a,b,c);


select * from t where a = :a and b = :b and c = :c;

we have an index that can find our rows very nicely.

or even:

select a, b, c from t where a = :a;

we have an index that can find our rows (by a) and return the needed data (a,b,c) without hitting the table at all.

Delete rows from index

Suraj Sharm a, April 09, 2007 - 8:05 am UTC

Tom,

I have two questions:

1. Please let me know the architecture of Btree index, how it stores the value and how it fetches the record faster?
2. I have a table say Employee and have an Btree index on EMPNO. I deleted some rows from the table, whether the rows from the index will also be deleted or will the block containing that entry of the rows will be free for further insert (we can you that block when we will insert new row in employee table) or will it be just free, but the same can not be used again until we will rebuild the index.
Thanks,
Suraj Sharma
Tom Kyte
April 09, 2007 - 10:52 am UTC

1) concepts guide, or if you like the way I say things - my most recent book "Expert Oracle Database Architecture"

2) see #1

Index on <> Operator

Maverick, April 23, 2007 - 12:55 pm UTC

Tom, I am trying to write a query that has a <> operator. It's taking for ever to run and as guessed it's doing full table scan on 2mill row table.

My query is in a Loop [of 20000 rows] and for each row it's hitting the table with a fulltable scan.

Here is the Pseudo-query [in a procedure]

Declare
  Cursor c1 is select * from my_table
               where <conditions>;

  next_date date:=null;
  
Begin
  [Loops 20000 times]
  for i in 1..c1
  loop
     select min(enroll_date) 
       into next_date
      from my_table     
     where id<>i.id
       and date>i.enroll_date;
  end loop;
end;


My_table is a table of 2mill rows with an index on Id.

I am guessing because of <> operator, it's doing a full table scan [is that true?].
How do I avoid this scenario and make Query use index?
Thanks,
Tom Kyte
April 23, 2007 - 4:46 pm UTC

what is the plan, it should be something like this;


Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=3 pr=2 pw=0 time=205 us)
1 FIRST ROW (cr=3 pr=2 pw=0 time=169 us)
1 INDEX RANGE SCAN (MIN/MAX) BT_IDX2 (cr=3 pr=2 pw=0 time=165 us)(object id 73164)


assuming index on (dt,id) columns...

Local Index on Partition key

Karteek, April 29, 2007 - 9:46 am UTC

Tom,

Why to create local index on PARTITION KEY. Like...we have composite range/list paritioned table - range on DATE and List on PRODUCT. And there are 2 local indexes each on DATE and PRODUCT columns.

CREATE INDEX PRT_SHIP_DATE ON SHIP_DELIV_PRODUCT
(SHIP_DATE) LOCAL (PARTITION....

CREATE INDEX PRT_SHIP_PROD ON SHIP_DELIV_PRODUCT
(PRODUCT) LOCAL (PARTITION....

I am not getting the advatange of these 2 local indexes...we really don't need to specify (extend) partition name in queries as Oracle can automatically prune to specific partition partition keys (SHIP_DATE, PRODUCT) are specified as predicates.

I hope you have understood my point...in simple...I need index on ship_deliv_ID, bcoz Oracle does not know where this ID is present in big DB...but why index (local) on Product - Oracle knows where records related to specific records are there (I mean in which partition)...

- Karteek

Tom Kyte
April 29, 2007 - 10:37 am UTC

we know the partition(s) they row(s) you are looking for exist in, but so what? You want to be able to retrieve a small set of rows from that big partition.

analogy: you have a million rows in the EMP table. We know what table your row is in. Why do we need an index on EMPNO?

substitute partition for table in the above question, hash partition on EMPNO. If you did "select * from emp where empno = ?" would you not want an index on the table/partition/whatever in order to be able to find that single record really fast?



Say you partitioned by range on ship_date and all 10,000,000 records for april are in the N list partitioned segment. You would like to see everything done on April 1st.

So do you want to a) full scan all N partitions looking for them? or b) range scan N local index partitions to find them?


Or even, say you have 10- 1,000,000 row subpartitions by list. You query 'where ship_date = ? and product = ?'

We can partition eliminate on product and range scan a single index subpartition on ship_date to find the row(s)... rather than full scan.

Karteek, April 30, 2007 - 8:01 am UTC

Seems like I failed to explain my question properly...

Another try...

create index emp_idx on emp(emp_id) -- I need this BTree index as emp_id has more distinct (cardinality) values and there are many employees.

create bitmap index sex_idx on emp(sex) -- here Bitmap is needed as there are less distinct valus, so that I can filter the result with set operations.

But...

create index region_idx on customer_sales(REGION) LOCAL - why is this necessary? my understanding is, though in this case we have distinct regions, as index is local, Oracle will internally maintain many indexes (s?), one index per each partition. So, we will have N indexes (internally) for N distinct regions. That means...each one index is solely for one region. my question here is why do we need an index on a cloumn with only one value (region1). I know, defintely my understanding is wrong (otherwise LOCAL idexes would not be existing), but I want to know where I am wrong and correct it. Could you please help, Tom.

- Karteek

Tom Kyte
April 30, 2007 - 9:48 am UTC

if you have a list partitioned table and only one value for that attribute in each partition, having an index on it seems "not sensible".

You have to tell me why you need it, I didn't create it.


I would disagree with your statement:

... create bitmap index sex_idx on emp(sex) -- here Bitmap is needed as there are less distinct valus, so that I can filter the result with set operations. ...


the choice of b*tree or bitmap is not just a function of number of discrete values. Just because it has two or three values does NOT mean I would use a bitmap index. Just because it has one million unique values does not PRECLUDE a bitmap index.

Karteek, April 30, 2007 - 10:10 am UTC

We have composite partitions (range - list)

SHOP_ID - Range
PRODUCT_ID - List

There are 2 LOCAL indexes -
1. ONLY on SHOP_ID
2. ONLY on PRODUCT_ID

My guess is, when our application was initially developed they might have thought LOCAL indexes differently and created index simply on PARTITION KEY ONLY. Now, We want to improvise the system - as a first step I want keep only indexes which are really useful.

We have about 4 nonpartitioned indexes + these 2 LOCAL indexes. Absolutely I don't see any advantage with these LOCAL indexes in our case.

Tom, I can not ask you to give me a solution for my case. But it would be great if you can dwell something on where ( with a simple scenario) LOCAL indexes can be good and why not other indexes; and similarlly for other indexes (global, nonparttitioned) - where they are good, why and why others are not good. Atleast, your help with a resource would be great (gone through Docs but didn't find a real scenario).

- Karteek

Tom Kyte
April 30, 2007 - 3:21 pm UTC

if you have partitions like:

shop_id < 100, product_id in ( 1,2,3,10,20 )
shop_id < 100, product_id in ( 302, 325,23523,5425, 5233 )
shop_id < 200, product_id in ( 1,2,3,10,20 )
shop_id < 200, product_id in ( 302, 325,23523,5425, 5233 )


which is RANGE on shop_id, list on Product_id - then, as stated, indexes could well make SENSE.


you seem to be saying, we have:

shop_id < 100, product_id in (1)
shop_id < 100, product_id in (2)
....

eg: your list is of size ONE. If so, a local index on product_id is LIKELY not useful

think about it - pretend a subpartition is just a TABLE (because it is under the covers in a very real way). Now, if you took a given subpartition would you index a given column?? If not, don't do it for the subpartition.

Karteek, May 01, 2007 - 1:49 am UTC

Thank you so much Tom! really useful insight

can we expect a post on your blog on partitions and indexes... in your full of scientific approach - may be with few best paractices that you might have followed on partition indexes.

Once again thanks for your above valuable reply...

- Karteek
Tom Kyte
May 01, 2007 - 9:57 am UTC

I wrote a chapter on it in a book - Expert Oracle Database Architecture.

Index Column Order

Vicki, April 30, 2008 - 7:58 am UTC

Hello Tom,

as I know the order of columns in an index has impact on how fast this index works.
We have created an index with some columns like

CREATE INDEX IND1 ON
TAB1(col1, col2, col3)
TABLESPACE ... PCTFREE .. STORAGE(INITIAL 40960 );
on one database. But after the running this script in the production database DBA mentioned, that the order of colums there changed (using select select COLUMN_NAME name, COLUMN_POSITION pos, DESCEND from dba_ind_columns where index_name = 'TAB1';)
Is this possible? How to avoid this? Or do we have a wrong understanding of the COLUMN_POSITION field?
Thanks a lot in advance.

Tom Kyte
April 30, 2008 - 10:54 am UTC

... Is this possible? ...

nope, not if you successfully ran that create index, I would have to presume you did not SUCCESSFULLY run the create if the order of the columns is different.

... how to avoid this? ...

ensure your scripts have some level of error processing...


Multiple indexes creation

A reader, August 01, 2009 - 6:23 pm UTC

Hi Tom,

We have a control table which holds the parameters needed for creating 20 local bitmap indexes on partitioned table (currently 10 million rows and growing). I wrote a PL/SQL to read from the table and I created indexes one by one. Is it possible to run all index statements in parallel through PL/SQL or some other way?
PS: We need to drop bitmap indexes before load as it was row by row processing through ETL tool, and was very slow.

Thanks

Tom Kyte
August 04, 2009 - 1:01 pm UTC

...
PS: We need to drop bitmap indexes before load as it was row by row processing through ETL tool, and was very slow.

.....


no, you should

a) set unusable
b) rebuild afterwards

and get rid of your table, not necessary - and lets you add drop indexes naturally without having to populate some table.


You can use dbms_job/dbms_scheduler to run many plsql routines simultaneously.

Unusable

A reader, August 04, 2009 - 3:46 pm UTC

Hi Tom,
Thanks - issue with making indexes unusable is that system is still being used (ad hoc queries) while new set of data is being loaded. However, users are aware of performance degradation during load. With making indexes unusable, users will start getting errors - index is in unsable state, and queries start failing?
Thanks
Tom Kyte
August 05, 2009 - 8:45 am UTC

... users will start getting errors ....

only if you make them get it.

and remember, if you were to

a) load table - a table, not a partition
b) index table
c) alter exchange to swap in new data

you will never set any index unusable. You will have continuous availability and no degradation in query response time due to indexes "going away"



In 10g and above, by default

ops$tkyte%ORA10GR2> show parameter unusable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes                boolean     TRUE
ops$tkyte%ORA10GR2>


if you are using really old software, you can enable that setting.


ops$tkyte%ORA10GR2> create table t as select * from scott.emp where 1=0;

Table created.

ops$tkyte%ORA10GR2> create bitmap index t_idx on t(job);

Index created.

ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats(user,'T',numrows=> 10000000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> insert into t select * from scott.emp;

14 rows created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set autotrace on explain
ops$tkyte%ORA10GR2> select ename, empno from t where job = 'PRESIDENT';

ENAME           EMPNO
---------- ----------
KING             7839


Execution Plan
----------------------------------------------------------
Plan hash value: 3530672349

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |   100K|  2539K|     9  (34)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T     |   100K|  2539K|     9  (34)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|       |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | T_IDX |       |       |            |          |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("JOB"='PRESIDENT')

ops$tkyte%ORA10GR2> alter index t_idx unusable;

Index altered.

ops$tkyte%ORA10GR2> select ename, empno from t where job = 'PRESIDENT';

ENAME           EMPNO
---------- ----------
KING             7839


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|  2539K|   217  (97)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    |   100K|  2539K|   217  (97)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("JOB"='PRESIDENT')

ops$tkyte%ORA10GR2> set autotrace off

Regarding Block Usage in Function Based index

Rajeshwaran, Jeyabal, August 05, 2009 - 9:03 am UTC

CREATE TABLE T(x NUMBER);

INSERT INTO T 
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL < = 1000000;
COMMIT;

CREATE OR REPLACE
FUNCTION MY_FUNC(X IN NUMBER) RETURN NUMBER
DETERMINISTIC AS
BEGIN
  RETURN (x*2);
END MY_FUNC;


CREATE INDEX T_IND ON T(SCOTT.MY_FUNC(x));

BEGIN
  dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T',estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE);
  dbms_stats.gather_index_stats(ownname=>USER,indname=>'T_IND',estimate_percent=>dbms_stats.auto_sample_size);
END;  

Rajesh@IRADSDB> ANALYZE INDEX T_IND VALIDATE STRUCTURE;

Index analyzed.

Rajesh@IRADSDB> SELECT height, blocks, name, lf_blks, br_blks
  2  FROM INDEX_STATS;

    HEIGHT     BLOCKS NAME                              LF_BLKS    BR_BLKS
---------- ---------- ------------------------------ ---------- ----------
         3       2368 T_IND                                2295          5

Rajesh@IRADSDB> show parameter db_block_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

Rajesh@IRADSDB> DECLARE
  2    P_OWNER VARCHAR2(200);
  3    P_NAME VARCHAR2(200);
  4    P_TYPE VARCHAR2(200);
  5  BEGIN
  6    P_OWNER := USER;
  7    P_NAME := 'T_IND';
  8    P_TYPE := 'INDEX';
  9  
 10    SHOW_SPACE(
 11      P_OWNER => P_OWNER,
 12      P_NAME => P_NAME,
 13      P_TYPE => P_TYPE
 14    );
 15  END;
 16  /
 FREE BLOCKS ............ 0
 TOTAL BLOCKS ............ 2368
 TOTAL BYTES ............ 19398656
 UNUSED BLOCKS ............ 24
 UNUSED BYTES ............ 196608
 last_used_extent_file_id ............ 151
 last_used_extent_block_id ............ 48713
 last_used_block ............ 8


1) INDEX_STATS shows My Leaf node occupies 2295 block and Branch Blocks occupies 5 Blocks. So total of 2300 Blocks occupied by Index.
2) But As per dbms_Space Package shows Total blocks = 2368 and UnUsed_blocks = 24, so total index size is 2344 Blocks (2368- 24).

So my Index Correct size is 2300 Blocks or 2344 Blocks. Can you please help me in this?
Tom Kyte
August 05, 2009 - 9:38 am UTC

you don't mention your tablespace configuration so I'll assume ASSM (automatic segment space management)

we use blocks inside of your segment to manage space, they are our blocks.

You have 2,295 leaf blocks. You have 5 branch blocks. You have other blocks that are used to manage the space allocation in the extents of the segment.

Index Size?

Rajeshwaran, Jeyabal, August 05, 2009 - 9:50 am UTC

Tom,

So my Index size is 2344 Blocks (2368- 24) that is returned by dbms_space package. rather than Leaf_block + Branch_block = 2300 Blocks.

please correct me if i am wrong.


Tom Kyte
August 05, 2009 - 10:00 am UTC

do you want to know the segment size? That is 2368, that is the space allocated to it.

do you want to know the number of blocks YOUR DATA consumes? That would be leaf+branch

You may interpret these numbers any way you see fit. I usually look at allocated space to say "I need X units of storage for this right now"

I would look at leaf/branch blocks if I was interested in studying the structure of the index.

What are you *interested* in, what will you do with this number...

but in short, you know what they all are - use any set of them for whatever you need.

increase freelist on index?

Andrew Markiewicz, January 20, 2010 - 11:02 am UTC

Tom,
In earlier posts the wait event "TX - index contention" is attributed to multiple concurrent processes needing to split a leaf block.
Would increasing the number of freelists on the index help in this situation? Or is it the case that since the separate processes are attempting to insert/update data resulting in the same required split, that it essentially serializes them regardless of the number of freelists?

I am hoping it's the former but think it's the latter since a freelist is just a linked list of available blocks under the HWM.

Thanks.
Tom Kyte
January 20, 2010 - 11:46 am UTC

only one guy at a time can work on a leaf block. it'll essentially serialize anyway.


index

A reader, January 20, 2010 - 7:41 pm UTC

excellent examples

Error in "secret sauce"

Rajeshwaran, Jeyabal, February 25, 2010 - 7:16 am UTC

Tom,

I am just working with "secret sauce" from your blog.
http://tkyte.blogspot.com/2006/01/something-about-nothing.html#comments

scott@10G> create index t_ind on t(object_id,0);

Index created.

scott@10G> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T',cascade=>true);

PL/SQL procedure successfully completed.


When replaced '0' with '1' ended up with below error.

scott@10G> create index t_ind on t(user_id,1);

Index created.

Elapsed: 00:00:00.72
scott@10G> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T',cascade=>TRUE);
BEGIN dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T',cascade=>TRUE); END;

*
ERROR at line 1:
ORA-03001: unimplemented feature
ORA-06512: at "SYS.DBMS_STATS", line 13437
ORA-06512: at "SYS.DBMS_STATS", line 13457
ORA-06512: at line 1


Is this a bug in Oracle Tom?
Tom Kyte
March 01, 2010 - 10:23 am UTC

see support note:559389.1

Order of columns in the index

Gary, April 07, 2010 - 2:36 pm UTC

Hi Tom

Environment is Oracle 10.2.0.2 EE on AIX 64-bit 5.3

I have a table with about 28 million rows and I recently recreated the primary key constraint that contains three (3) columns.

Right after I created the PK I ran a query to see all the indexes on the table and what columns were in each one.

The PK I had just created showed the columns in a different order than what I had specified in the ALTER TABLE ADD CONSTRAINT command.

Here is the output:
owb_rtt@dwp1> alter table f5741029_planning_add_hist add constraint F5741029_HIST_PK
  2  primary key (rundate, SHORT_ITEM_NBR_PAITM, BUSINESS_UNIT_PAMCU)
  3  using index tablespace df_lg_d;

Table altered.


owb_rtt@dwp1> @index_list
Enter value for table_name: f5741029_planning_add_hist

INDEX_NAME                     UNIQUENES COLUMN_NAME                    COLUMN_POSITION
------------------------------ --------- ------------------------------ ---------------
F5741029_HIST_MCU_ITM          NONUNIQUE BUSINESS_UNIT_PAMCU                          1
F5741029_HIST_MCU_ITM          NONUNIQUE SHORT_ITEM_NBR_PAITM                         2

F5741029_HIST_PK               UNIQUE    RUNDATE                                      1
F5741029_HIST_PK               UNIQUE    BUSINESS_UNIT_PAMCU                          2
F5741029_HIST_PK               UNIQUE    SHORT_ITEM_NBR_PAITM                         3


As you can see I created the constraint/index with the SHORT_ITEM_NBR_PAITM column before the BUSINESS_UNIT_PAMCU column but in the query results they are shown reversed.

The 'index_list' query looks like this:

break on index_name skip 1 duplicates

col column_name format a30

select ui.index_name, 
       ui.uniqueness,
      uic.column_name, 
      uic.column_position
from user_indexes ui,
     user_ind_columns uic
where ui.index_name = uic.index_name
  and uic.table_name = upper('&table_name')
order by ui.index_name, uic.column_position;


What happened?

Thanks very much as usual for your efforts on this site, it's the best!!

-gary
Tom Kyte
April 13, 2010 - 7:39 am UTC

I'd guess that the index F5741029_HIST_PK already existed.

ops$tkyte%ORA10GR2> create table t ( a int, b int, c int, d int );

Table created.

ops$tkyte%ORA10GR2> create UNIQUE index t_pk on t(a,c,b);

Index created.

ops$tkyte%ORA10GR2> alter table t add constraint t_pk primary key(a,b,c) using index tablespace users;

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select ui.index_name,
  2         ui.uniqueness,
  3        uic.column_name,
  4        uic.column_position
  5  from user_indexes ui,
  6       user_ind_columns uic
  7  where ui.index_name = uic.index_name
  8    and uic.table_name = 'T'
  9  order by ui.index_name, uic.column_position;

INDEX_NAME                     UNIQUENES COLUMN_NAME          COLUMN_POSITION
------------------------------ --------- -------------------- ---------------
T_PK                           UNIQUE    A                                  1
T_PK                           UNIQUE    C                                  2
T_PK                           UNIQUE    B                                  3



and we found that we didn't need to create yet another index. If you want it, you can create it yourself. You cannot (should not) count on a constraint to create an index for performance reasons unless you are excessively explicit:

ops$tkyte%ORA10GR2> create table t ( a int, b int, c int, d int );

Table created.

ops$tkyte%ORA10GR2> create UNIQUE index t_pk on t(a,c,b);

Index created.

ops$tkyte%ORA10GR2> alter table t add constraint t_pk primary key(a,b,c)
  2  using index <b>(create UNIQUE index t_pk_new on t(a,b,c) tablespace users)</b>;

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select ui.index_name,
  2         ui.uniqueness,
  3        uic.column_name,
  4        uic.column_position
  5  from user_indexes ui,
  6       user_ind_columns uic
  7  where ui.index_name = uic.index_name
  8    and uic.table_name = 'T'
  9  order by ui.index_name, uic.column_position;

INDEX_NAME                     UNIQUENES COLUMN_NAME          COLUMN_POSITION
------------------------------ --------- -------------------- ---------------
T_PK                           UNIQUE    A                                  1
T_PK                           UNIQUE    C                                  2
T_PK                           UNIQUE    B                                  3
T_PK_NEW                       UNIQUE    A                                  1
T_PK_NEW                       UNIQUE    B                                  2
T_PK_NEW                       UNIQUE    C                                  3

6 rows selected.

Whoops, forgot the index still there after the constraint drop

Gary, April 13, 2010 - 2:49 pm UTC

Hi Tom

Awesome as usual.

I momentarily :-) forgot that in 10g the index doesn't get dropped when the PK constraint is dropped.

I was rearranging indexes between a couple of similar tables.

Thanks very much!!

-gary

..Analyze index validate structure

A Reader, January 04, 2013 - 4:50 pm UTC

Tom
How to make the following to run quicker for a set of indexes on a set of tables.

analyze index t_idx validate structure
?
facts:
1) we cannot use parallelism here.
2) we cannot run it parallel ( DIY parallelism) - by running separate statements for seprate indexes/

kindly comment
regards

Tom Kyte
January 14, 2013 - 10:10 am UTC

why is #2 a fact? just use separate sessions and analyze indexes on different tables - each session has their own index_stats "v$" view - index stats lives in the PGA, it contains the last result of the last index analyze in your session.


#2 is not a fact.

..Analyze index validate structure

A Reader, January 14, 2013 - 8:01 pm UTC

My bad !
regd #2

Sorry Tom I didnt tried it. Now I knew it .
thanks for awakenming

regards

JSON Multi value index in Oracle 21c

Rajeshwaran, Jeyabal, October 19, 2021 - 9:27 am UTC

Team,

create table stage as select * from all_objects;

create table t (x json);
insert into t(x)
select json_object( 'owner_name' value owner, 'object_type' value json_arrayagg( json_object( 'object_name' value object_name ,
    'object_type' value object_name,
    'object_id' value object_id ) returning clob ) returning clob)
from stage     
group by owner;
commit;

select jt.*
from t, json_table( x, '$.object_type[*]'
    columns(
        object_id number path '$.object_id') ) jt
where rownum <= 5 ;
create multivalue index t_idx on t t1( t1.x.object_type.object_id.number() );
exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

demo@XEPDB1> set autotrace traceonly exp
demo@XEPDB1> select *
  2  from t
  3  where json_exists( x, '$.object_type?(@.object_id == 75909)' );

Execution Plan
----------------------------------------------------------
Plan hash value: 1148600783

---------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |     1 |   605 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T     |     1 |   605 |     1   (0)| 00:00:01 |
|   2 |   HASH UNIQUE                       |       |     1 |   605 |            |          |
|*  3 |    INDEX RANGE SCAN (MULTI VALUE)   | T_IDX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access(JSON_QUERY("X" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$.object_type.object_id.number()' RETURNING NUMBER ASIS  WITHOUT ARRAY WRAPPER
              ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH MULTIVALUE)=75909)

demo@XEPDB1> 
demo@XEPDB1> select jt.*
  2  from t, json_table( x, '$.object_type[*]'
  3      columns( object_id number path '$.object_id' ) ) jt
  4  where jt.object_id = 75909;

Execution Plan
----------------------------------------------------------
Plan hash value: 1386969201

-------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |    21 | 12705 |  1482   (1)| 00:00:01 |
|   1 |  NESTED LOOPS          |      |    21 | 12705 |  1482   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL    | T    |     1 |   603 |  1453   (1)| 00:00:01 |
|*  3 |   JSONTABLE EVALUATION |      |       |       |            |          |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(JSON_EXISTS2("X" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$?(@.object_type[*].object_id==75909)' FALSE ON ERROR)=1)
   3 - filter("P"."OBJECT_ID"=75909)

demo@XEPDB1> select jt.*
  2  from (
  3  select x
  4  from t
  5  where json_exists( x, '$.object_type?(@.object_id == 75909)' )
  6       )t1, json_table( x, '$.object_type[*]'
  7       columns( object_id number path '$.object_id' ) ) jt
  8  /

Execution Plan
----------------------------------------------------------
Plan hash value: 3213954389

----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |     1 |   607 |    30   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                        |       |     1 |   607 |    30   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T     |     1 |   605 |     1   (0)| 00:00:01 |
|   3 |    HASH UNIQUE                       |       |     1 |   607 |            |          |
|*  4 |     INDEX RANGE SCAN (MULTI VALUE)   | T_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |   JSONTABLE EVALUATION               |       |       |       |            |          |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access(JSON_QUERY("X" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$.object_type.object_id.number()' RETURNING NUMBER ASIS  WITHOUT ARRAY WRAPPER ERROR
              ON ERROR NULL ON EMPTY NULL ON MISMATCH MULTIVALUE)=75909)

demo@XEPDB1> set autotrace off
demo@XEPDB1>


Questions:

1) JSON_TABLE doesnt pick this multivalue index by default, if so then json_exits plus json_table is always the way to go for index friendly cases?

Connor McDonald
October 21, 2021 - 1:56 am UTC

Picking loosely related questions in order to post a new question in is not how the site works, because we lose the granularity for future community members to find answers.

JSON Multi value index in Oracle 21c

Rajeshwaran Jeyabal, October 21, 2021 - 3:52 am UTC

Thanks Connor, will take care of this in future and will make this as a new question for further follow up.
Connor McDonald
October 22, 2021 - 3:49 am UTC

Appreciate your understanding