Performance Tuning :: How Length Of Column Width Effects Index Performance
Sep 30, 2010
How the length of column width effects index performance?
For example if i had IOT table emp_iot with columns:
(id number,
job varchar2(20),
time date,
plan number)
Table key consist of(id, job, time)
Column JOB has fixed list of distinct values ('ANALYST', 'NIGHT_WORKED', etc...).
What performance increase i could expect if in column "job" i would store not names but concrete numbers identifying job names.
For e.g. i would store "1" instead 'ANALYST' and "2" instead 'NIGHT_WORKED'.
SELECT CURRENTSTEP FROM (SELECT ( WFENTRY.NAME || ',' || CURRENTSTEP.STEP_ID ) AS CURRENTSTEP, (CASE WHEN WFENTRY.NAME IN
[Code]...
in this query I am concatenating tow columns , I use this query as a sub query in my other queries and filter the results with and CURRENTSTEP = ?
here is how I use it
select sys_audit_id from ( SELECT * FROM (SELECT F.FINDING_NUMBER,
[Code]....
I saw adding this as a subquery with the filter and CURRENTSTEP = ? is slowing my query very much , as this is a derived column i cannot add index then how to improve performance for this subquery ?
We have a table called address and having the address fields and city ,state etc. The table will store huge amount of data .We need to query on the table. I would like to know how can we fasten the query and improve the performance of the query by creating index on these columns...Query is given below . note that the nullable columns can have data
select distinct S.ID ID from ods.hso_Scheduled H, ods.SO_SCHEDULED S where S.insertion_date >= to_date('01-DEC-2011') and S.insertion_date < to_date('01-FEB-2012') and H.ID=S.ID
Both the involved tables, HSO_SCHEDULED is having 15 million records and SO_SCHEDULED table is having 7 million records.
I have created following indexes on these tables:
Indexes on SO_SCHEDULED: Index name Column name SS_IDX1ID, SO_SUB_ITEM__ID SS_IDX2INSERTION_DATE SS_IDX3ID, INSERTION_DATE SS_IDX4ID, SO_SUB_ITEM__ID, INSERTION_DATE SO_SCHEDULED_ID_PKID
I came across situation where a Nullable column is not using index for 'order by' clause. I added Not Null condition in the 'where' condition but it wasn't useful. I don't wanted to make composite index with not nullable column or with constant or modify column to 'Not Null'
So I carried out test cases and during which I found that in one case the sql statement does 'fast full scan' for data access but does not use index for 'order by' sorting
here are the steps
Initially I kept the column Nullable
SQL> create sequence s5; Sequence created.
SQL> create table t5 as select s5.nextval id,a.* from dba_objects a where rownum<1001; Table created.
SQL> set pages 100 SQL> select column_name,nullable from user_tab_columns where table_name='T5';
Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 5
Rows Row Source Operation ------- --------------------------------------------------- 1000 SORT ORDER BY (cr=16 pr=0 pw=0 time=4771 us) 1000 TABLE ACCESS FULL T5 (cr=16 pr=0 pw=0 time=1157 us)
Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 68 0.00 0.00 SQL*Net message from client 68 49.49 49.72 ********************************************************************************
select /*+ index(t i5) */ * from t5 t where id is not null order by id
Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 5
Rows Row Source Operation ------- --------------------------------------------------- 1000 TABLE ACCESS BY INDEX ROWID T5 (cr=150 pr=0 pw=0 time=5167 us) 1000 INDEX FULL SCAN I5 (cr=71 pr=0 pw=0 time=3141 us)(object id 4673065)
Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 69 0.00 0.00 SQL*Net message from client 69 22.89 28.04
Now I modified the 'id' column to Not Null
SQL> alter table t5 modify id not null;
SQL> set pages 100 SQL> select column_name,nullable from user_tab_columns where table_name='T5';
COLUMN_NAME N ------------------------------ - ID N OWNER Y OBJECT_NAME Y SUBOBJECT_NAME Y OBJECT_ID Y DATA_OBJECT_ID Y OBJECT_TYPE Y CREATED Y LAST_DDL_TIME Y TIMESTAMP Y STATUS Y TEMPORARY Y GENERATED Y SECONDARY Y
Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 5
Rows Row Source Operation ------- --------------------------------------------------- 1000 SORT ORDER BY (cr=16 pr=0 pw=0 time=2398 us) 1000 TABLE ACCESS FULL T5 (cr=16 pr=0 pw=0 time=1152 us)
Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 68 0.00 0.00 SQL*Net message from client 68 37.74 37.91 ********************************************************************************
Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 5
Rows Row Source Operation ------- --------------------------------------------------- 1000 TABLE ACCESS BY INDEX ROWID T5 (cr=150 pr=0 pw=0 time=4166 us) 1000 INDEX FULL SCAN I5 (cr=71 pr=0 pw=0 time=3142 us)(object id 4673065)
Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 68 0.00 0.00 SQL*Net message from client 68 8.28 8.45
Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 5
Rows Row Source Operation ------- --------------------------------------------------- 1000 SORT ORDER BY (cr=6 pr=0 pw=0 time=1342 us) 1000 INDEX FAST FULL SCAN I5 (cr=6 pr=0 pw=0 time=1093 us)(object id 4673065)
Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 68 0.00 0.00 SQL*Net message from client 68 1.88 1.89
Questions are
1) Why adding 'where id is not null wasn't enough for the index to get used in 'order by'? 2) While we got 'fast full scan' why index wasn't used for 'order by' clause? 3) Do we need the indexed column in where clause for being used in 'order by clause' too? 4) Do we need 'order by' clause if we are selecting only the indexed column with sequence generated values?
I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.
where @var is user supplied input at runtime...We had a index on a.c2 . The CBO would use this index to generate an opitimised query plan.We found some records from table "b" were dropping due to inner join. So we made a change in join. It'd be like
a.c1(+)=b.c1 and nvl(a.c2,@var)=@var
This query is no longer using the index, instead its doing a full table scan causing the query to slowdown.I have tried creating index on nvl(a.c2,'31-dec-9999')
But the CBO won't use it.Anyway to create index on this col so that full table scan can be avoided?
We have a DELETE statement when coming from application is not using index but when run from Toad or SQLplus as same user uses index. Explain plan also shows using index.I did a query on v$sql below is the output of the query( I have attached the same as a txt file). All the stats are up to date and confirmed from the developer the variable B1 is using the same datatype as column MAXMKY.
SQL_TEXTSQL_ID DISK_READSOPTIMIZER_HASH_VALUE DELETE LOTA WHERE MAXMKY=:B1 2g2prrp3z56ah19,099,1891,846,735,884 DELETE LOTA WHERE MAXMKY=:B1 2g2prrp3z56ah0 1,846,735,884 OPTIMIZER_COST HASH_VALUEPLAN_HASH_VALUE MODULEPARSING_SCHEMA_NAME
I am working on a query for a feedback response system which is going to be targeted at the common case when the user only want the most recent 10-20 rows in the feedback table. My though is to create an index on the date column, do a sort in an inner query and rownum <= in an outer query. This works as I expect when I am only querying the main table (lookup by index with a stop key), but when I start joining the main table to attribute tables I end up with a full table scan of the main table with the stop key applied after all the joins are completed, the index is nowhere to be found.
CREATE TABLE attr1_tbl(attr1_id NUMBER NOT NULL, attr1 VARCHAR2(10) NOT NULL, CONSTRAINT attr1_pk PRIMARY KEY (attr1_id)); CREATE TABLE attr2_tbl(attr2_id NUMBER NOT NULL, attr2 VARCHAR2(10) NOT NULL, CONSTRAINT attr2_pk PRIMARY KEY (attr2_id)); CREATE TABLE attr3_tbl(attr3_id NUMBER NOT NULL, attr3 VARCHAR2(10) NOT NULL, CONSTRAINT attr3_pk PRIMARY KEY (attr3_id)); [code]....
One thing I noticed was that when no data is selected from the attribute tables, even if they are joined in the query, the CBO throws them out of the plan and only accesses the main table. With the foreign keys this makes sense and really just disqualified my first thought that maybe I was missing a foreign key or not null constraint somewhere.
I also added the cardinality hint to overcome the chance that in my test case there was so little data that index access is not worth it.
* 35 | ID TABLE ACCESS BY INDEX ROW | S_ORG_EXT | 3064K| 2472M| | 1 (0)| 00:00:01 | | 36 | INDEX FULL SCAN | S_ORG_EXT_U1 | 14 | | | 1 (0)| 00:00:01 |
Predicate Information (identified by operation id): --------------------------------------------------- 35 - filter("T2"."ACCNT_FLG"<>'N' AND ("T2"."INT_ORG_FLG"<>'Y' OR "T2"."PRTNR_FLG"<>'N'))
This unselective index scan on step 36 of the explain is returning 14 rows but optimizer is selecting 3064 K rows from the table .
I tried creating combined index on all 3 columns mentioned in the predicates for 35th step , but that is not utilized .
how to index this whole expression ::--
(ACCNT_FLG<>'N' AND (INT_ORG_FLG<>'Y' OR PRTNR_FLG<>'N'))
Something like CREATE INDEX XYZ on table((ACCNT_FLG<>'N' AND (INT_ORG_FLG<>'Y' OR PRTNR_FLG<>'N')) compute statistics ;
I have the following problem. When I used in the IN-Statement fixed values e.q. 197321,197322,197323 ..., the index i_tab2_index works fine (index range scan).
But when I used in the IN-Statement an Sub-Select, the index i_tab2_index doesn't work (fast full scan)!My scale indices and used Selects:
CREATE INDEX i_tab1_index ON tab1 ( datum, flag_inst ); CREATE INDEX i_tab2_index ON tab2 ( tab2Idx, kontro ); SELECT count(epidx) as rowAnz FROM tab2 WHERE tab2Idx IN ( SELECT tab1IDX FROM tab1 WHERE datum BETWEEN '20120117' AND '20120117' AND flag_inst = '1' ) AND kontro = '9876521' [code]...
get all the unused index in the system , if i put this query in batch job and execute it every night upto one months and store its data in a table and after one months i can get all the used indexes and left would be our unused indexes.
select distinct p.object_name c1 from dba_hist_sql_plan p, dba_hist_sqlstat s
I have a table whose size is 2.3 GB and there are two indexes on it. One index is based on a Date column whose size is 900 MB, and the Other index consists of 5 columns including the date column, and the size is almost 2GB. But when i query the table using the Date column, it is doing a range scan on the second index which is almost the same size as the table. why is it not using the first index? What steps should i take so that it uses the First index without passing hints.
mbr has 60,000 rows and member has 60,000 rows approx. two tables have indexes on ssn, and citi_no on them.
PK of mbr : mbr_id PK of member : mbr_id
other columns are not PK, and have no index on it.
I'm wondering why the statment doesn't use index while ssn and citi_no have index.
MERGE INTO mbr t USING (SELECT mbr_id,citi_no FROM member) a ON (t.ssn = a.citi_no) WHEN MATCHED THEN UPDATE SET t.asis_mbr_id = a.mbr_id where t.ssn not in(select ssn from mbr group by ssn having count(*) > 1)
I have to create indexes on foreign key columns ,now if composite index is already there with foreign key column then that will work or i will have to create a single column index.
i am trying to find the index want to rebuild or not for that i have analyzed that index after that i don't know how to calculate the ration could any one steps to do calculate the following ratio
Run the ANALYZE INDEX command on the index to validate its structure and then calculate the ratio of LF_BLK_LEN/LF_BLK_LEN+BR_BLK_LEN and if it isn?t near 1.0 (i.e. greater than 0.7 or so) then the index should be rebuilt. Or if the ratio BR_BLK_LEN/ LF_BLK_LEN+BR_BLK_LEN is nearing 0.3.
Select tag0.TAG_VALUE pid, tag1.TAG_VALUE, tag2.TAG_VALUE From TAGGER.TAGGABLE_RESOURCE r , TAGGER.TAG tag0 , TAGGER.TAG tag1 , TAGGER.TAG tag2 where 1=1
[code]....
This runs in about 400ms. Now I replace this:
ANDtag0.TAG_TYPE in (4602, 5228) ANDtag1.TAG_TYPE in (4612, 5225) ANDtag2.TAG_TYPE in (4613, 5226)
with this:
ANDtag0.TAG_TYPE in (select COLUMN_VALUE from ( select * from table( TAGGER.GET_IDS_OF_SIMILAR_TAG_TYPES('Patient ID') ) x1 )) ANDtag1.TAG_TYPE in (select COLUMN_VALUE from ( select * from table( TAGGER.GET_IDS_OF_SIMILAR_TAG_TYPES('Patients Sex') )x2 )) ANDtag2.TAG_TYPE in (select COLUMN_VALUE from ( select * from table( TAGGER.GET_IDS_OF_SIMILAR_TAG_TYPES('Patients Birth Date') ) x3 ))
So instead of hard coding the IDs there is a function that looks them up. The function itself is reporting that it runs in 0ms. But when I run the new query:
Select tag0.TAG_VALUE pid, tag1.TAG_VALUE, tag2.TAG_VALUE From TAGGER.TAGGABLE_RESOURCE r , TAGGER.TAG tag0 , TAGGER.TAG tag1 , TAGGER.TAG tag2 where 1=1
[code]....
it takes around 6s to run. I have looked at the explain plans it it seems as though the function based approach is triggering a full table scan of 'TAG'.
I have tried it with query hints to use index, but it doesn't change the execution plan, or the query time.
The explain plan for the quick query is:
PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- Plan hash value: 1031492929 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
[code]....
And the slow one is:
PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Plan hash value: 2741657371 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |Te ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20M| 1602M|
Name Type ------- -------------- ID NUMBER(8) USERID NUMBER(8) SORT_TEXT VARCHAR2(100) TEXT VARCHAR2(1000) DATE DATE VALID VARCHAR2(1) CODNEW NUMBER(10)
The table has a normal index for the userid column.
There is a query that looks for the differents CODNEW for a USERID but allways the CODNEW has to be greater than 2248833
select codnew from news-comment where userid=2914655 and valid='N' and codnew>2248833
I have created a new index for this kind of querys
create index coment_new_IDX on news_comment (CASE WHEN codnew >2248833 and valid='N' THEN userid ELSE NULL END )
but oracle doesn't use it. I have used a hint to force it but doesn't run.
I have create a table with 8 million records and 2 different indexes using 2 different columns (columns name NUM1 & NUM2) on that table. First indexed column (NUM1) values have many different values (1,2,3... etc).
Second indexed column (NUM2) values have only 2 different values. 7999999 records values is same("A") and remaining one record values is different("B").
Query1: select * from tbl where num1=val
Query2: select * from tbl where num2='B'
I have compare explain plan both queries, but Query2 doesn't use predefined index. Why Oracle don't use my redefined index at column NUM2?
I have column containing three values:-N,E,Y.I want to get results with only E and Y values.Is it it possible to create index which would not look for N values.
CREATE TABLE prod_vendor_record ( vendor_record_seq_no NUMBER NOT NULL, study_seq_no NUMBER NOT NULL, vendor_subject_seq_no NUMBER NULL, control_dataset_seq_no NUMBER NOT NULL, checksum NUMBER NOT NULL, processing_flag VARCHAR2(1) NULL,
[code]....
and executing below query on those tables-
insert into prod_temp_vendor(vendor_record_seq_no,checksum,rownumber,transaction_type,iu_flag) select vr.vendor_record_seq_no, tvr.checksum, tvr.rownumber, tvr.transaction_type, 'U' from prod_vendor_record vr, prod_temp_vendor_record_20000 tvr where vr.study_seq_no=25707 and vr.control_dataset_seq_no=3910 and vr.key_hash=tvr.key_hash and dbms_lob.compare(vr.key_col_val, tvr.key_col_val) = 0 and tvr.error_flag is null;
let me know on which columns of PROD_VENDOR_RECORD table to apply index to make processing faster. As I tried to build index like below-
CREATE INDEX idx_prod_vendor_record ON prod_vendor_record ( study_seq_no, control_dataset_seq_no, key_hash ) /
But it is not being used by above query (see execution plan)
I have been reading Oracle documentaion about access paths, got strucked at the concept 'Leading columns in index'. what is meant by 'Leading columns of an index', how to find/judge them that they are leading columns.
This table has a query where one of the condition is AND STATUS <> 'C'
Now the data is as following
select count(*) record_count, status from new_business group by status;
record_countstatus 4298025C 15N 13Q 122S
I want to know if following index would be useful in this case while the condition in where clause is
"AND STATUS <> 'C'"
create index nb_index_1 on new_business(case when status in('N','Q','S') then 1 else NULL end); Or create index nb_index_1 on new_business(case when status ='N' then 'N' when status='Q' then 'Q' when status='S' then 'S' else NULL end);
I tried it on a sample table but the index is simply not picked up even when hinted following are the db level settings
I create a view on production server which takes almost 10 to 12 minutes when it shows data. this view contains 3 or 4 tables on which all primary and unique columns have indexes.which index will be better for fast retrieval of data .
We have a large customer table so first thought was to partition.Also we see two union alls in the plan - can we introduce parallelism? Below is the plan - have attached a text file if difficult to read
