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 ?
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 ;-)
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>
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
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.
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.
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.
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.
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.
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.
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
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
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,
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,
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)
/
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,
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,
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?
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!)
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?
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)?
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).
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
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!
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
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.
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.
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
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.
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
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
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
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
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 |
----------------------------------------------------------------
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
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
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
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
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
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
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
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
**************************
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.
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.
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
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,
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
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
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
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
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.
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
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
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?
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.
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.
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?
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
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
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?
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.
October 22, 2021 - 3:49 am UTC
Appreciate your understanding