Performance Tuning :: Force Index If Table Not Using Index?
Aug 9, 2013How to force an index if the table not using the index?
View 10 Replies
ADVERTISEMENT
Performance Tuning :: Force Subquery To Use Index?
Aug 5, 2010I have the following 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]....
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|
[code]....
Performance Tuning :: How To Force SQL To Use Index Range Scan
Jul 15, 2011How can i force a sql use index range scan?
View 10 Replies View RelatedPerformance Tuning :: Force Index Unique Scan?
Jun 14, 2012Is there any hint to force "Index Unique Scan" over "Index Range Scan".
My query is generating different plans with the above two, and very slow when it uses "Index Range Scan".
Performance Tuning :: Local Index Versus Global Index On Partitioned Table
Jun 28, 2011I 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.
View 6 Replies View RelatedPerformance Tuning :: Index With NVL / Query Is No Longer Using Index
Nov 19, 2010I have a query which had a join:
a.c1=b.c1 and a.c2=@var
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?
Performance Tuning :: Index On Range-List Subpartitions Table Using Template
Oct 24, 2011I created table Rang pertitionned, and List subpartitionned. My table is Interval partitionning. My subpartition is template based as is :
PARTITION BY RANGE ( DINFOIDENTITE )
INTERVAL ( (NUMTOYMINTERVAL(1,'MONTH')) )
SUBPARTITION BY LIST ( AVANT_DERNIER_MATCLE )
[Code]....
i would have the same repartition with 10 differents tablespaces, one for each subpartition.
I search on Oracle documentation this morning, but the only thing i've found is that this option is possible with the STORE IN clause, but only for HASH partitionning.
Is there a way to specify STORAGE clause with a template for my indexes ?
Performance Tuning :: Difference Between Index Rebuild And Index Rebuild Online?
Jan 10, 2011What is the difference between index rebuild and index rebuild online.
View 3 Replies View RelatedPerformance Tuning :: Composite Index - Explain Plan Full Table Scan On Lookup_fein?
May 12, 2011I have created an non unique index lk_fein on lookup_fein( code,map_id,trash). When I check the explain plan it does a full table scan on lookup_fein. if I force it to use index by it does and the cost also decreases.
SQL> SELECT WORK_FEIN,
2 NON_FEIN ,
3 FI_FEIN ,
4 MFEIN ,
5 TOTAL_FEIN ,
[code]...
Performance Tuning :: How Length Of Column Width Effects Index Performance
Sep 30, 2010How 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'.
Performance Tuning :: Parallel Index With Non-partitioned No-parallel Table?
Apr 30, 2012If we have not set parallel degree for a table then we can ( try to ) force parallel execution on a table using a parallel hint Does this 'parallelism' works on the index search in the query as well?
In which situations non-parallel non-partitioned table but parallel index (degree>2) will facilitate a query?
Performance Tuning :: SQL Not Using Index From Application?
Jun 6, 2013We 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
[code].....
Performance Tuning :: STOPKEY And Index Use
Nov 6, 2010I 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.
Performance Tuning :: How To Create Index
Mar 30, 2013I am going through this scenario:
* 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 ;
Performance Tuning :: Index Creation?
Aug 9, 2010is there any way to reduce the index creation time.
in my case one index creation took 5 minute and there are 5 indexes , so it took 25 minutes.
Performance Tuning :: Index With IN Statement
Jan 18, 2012I 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]...
Performance Tuning :: Index Monitoring
Aug 26, 2010get 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
[Code]....
Performance Tuning :: Proper Index Is Not Used
Oct 17, 2012I 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.
View 4 Replies View RelatedPerformance Tuning :: How To Use Index On (ON In MERGE Statement)
Apr 6, 2011mbr 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)
Performance Tuning :: Index On Foreign Keys?
Jul 22, 2010I 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.
View 17 Replies View RelatedPerformance Tuning :: Clustering Factor In Index?
Dec 15, 2010I am just curious to know what is clustering factor in index and How is it impartant in a sql tuning perspective ?
View 3 Replies View RelatedPerformance Tuning :: How To Find Index To Rebuilt
Oct 3, 2011i 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.
Performance Tuning :: Join Condition In Index?
Mar 14, 2012For a hash join statement, is it beneficial to have the join condition objects in the index as well as the objects in the where clause?
View 19 Replies View RelatedPerformance Tuning :: Why Oracle Doesn't Use The Index
Dec 2, 2010I have a table "NEWS_COMMENT" like this:
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.
Performance Tuning :: Why Oracle Do Not Use Predefined Index
Apr 7, 2011Here, let me explain:
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?
Performance Tuning :: Possible To Create Index Which Would Not Look For N Values
Dec 6, 2011I 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.
View 13 Replies View RelatedPerformance Tuning :: On Which Columns To Apply Index
Mar 5, 2012I have two tables like below-
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)
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 5168 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 212 | 445K| 5168 (1)| 00:01:03 | Q1,02 | P->S | QC (RAND) |
[code]....
Performance Tuning :: Index Leading Columns?
Dec 14, 2010I 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.
The url where I am reading is[URL]...
Performance Tuning :: Function Based Index Is Not Being Used
Feb 22, 2012I have a table which has 4M records
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
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
I tried it 'query_rewrite_integrity' with 'trusted' as well .
Performance Tuning :: Derived Column Index
Apr 2, 2011here is my query
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 ?