Performance Tuning :: Can Use Oracle Hint To Use Index In A Query Which Uses UNION
Mar 31, 2011
I have a SQL query where I am making UNION of two select statements. The table that I am joining in each select statement have indexes defined for those tables.
Now the UNION of the two select statements again in enclosed in an inline view , from which I fetching my final field values.
The select statements inside the inline view returns huge number of row (like 50 million rows).
The whole query fails with time out.
Is there a way to pass Oracle Hints so that Oracle uses indexes?
I have a SQL query where I am making UNION of two select statements. The table that I am joining in each select statement have indexes defined for those tables.
Now the UNION of the two select statements again in enclosed in an inline view , from which I fetching my final field values.
The select statements inside the inline view returns huge number of row (like 50 million rows).
The whole query fails with time out.
How can I optimize this query further?
Is there a way to pass Oracle Hints so that Oracle uses indexes?
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
select serialnumber from product where productid in (select /*+ full parallel(producttask 16) */productid from producttask where startedtimestamp > to_date('2013-07-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and startedtimestamp < to_date('2013-07-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and producttasktypeid in
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?
Oracle UNION ALL performance issue: when I try to run below SQL query separately SQL part1 and SQL part2 it takes some seconds only but if I run together with group by and without group by it take much time.
SELECT AVG(date_completed-login_date),to_char(to_date(login_date), 'YYYY') as wYear FROM ( SELECT test.date_completed 'date_completed',sample.login_date 'login_date') FROM sample test where (some conditions) ) ---SQL part 1 UNION ALL
I have a query with FULL hint that is behaving in a strange manner. The query fetches around 700000 of data. Sometimes it fetches the data with the hint and sometimes it does not fetch any data with the hint and then I have to remove the hint and have to fetch the data. Below is the query,
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
Does parallel hint works in cursor queries? The cursor query is something like :
cursor c is select /*+ parallel(s,8) */ from table ref_tab s ---- >> <where condition>;
The table ref_tab hold data for a single day at any point of time and gets truncate before loading the next days data.On average the table holds around 7 million rows and doesn't contains any index (think that's fine as all together we are loading the whole set).And, we are using bulk logic with save exceptions to open the cursor and load the data into the target table.
I have an APP that truncates tables and loads data, which in turn makes the stats stale. I ran the query advisor (see attachment) and of course it ecommends running stats or accept a profile.I really don't want to do that as it may cause a load on my DB.
In turn, I would like to consider having my APP team change the query to pass a hint to use the best query plan.syntax to pass the hint to emulate good attached plan? Or is this a bad way to proceed?
select /* INDEX FAST FULL SCAN PK_PLACEMENT_REQUEST_QUEUE */ sum(lastshares) as "ROSEN" from nyeo.fix_exec_reports fer, nyeo.placement_request_queue q, nyeo.nyeo_block_control bc where fer.clordid = q.sequence_number and q.blockid = bc.blockid and upper(bc.deskname) like '%ROSEN%'
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?
now my code is using UNION ALL to get output as in table2 select col1,col1,'Segment1' col3,Segment1 col4 from table1 union all select col1,col1,'Segment2' col3,Segment2 from table1 union all select col1,col1,'Segment3' col3,Segment3 from table1
But the problem is the performance is realy bad.Is there any way i can do this without using union all? The time that take to execute this is not exceptable.
We have large tables 60-70 GB having 120 million records. We have to perform index rebuild frequently which takes significant time to complete and effects database performance too. how we can use index Coalesce? what are its benefits, coalesce results in performance gain?
1DGT_ITEMEFFORTDATA_DAILYHCLT_IDX_DGT_IFDITEMID4 2DGT_ITEMEFFORTDATA_DAILYHCLT_IDX_DGT_IFDITEMTYPE3 3DGT_ITEMEFFORTDATA_DAILYHCLT_IDX_DGT_IFDOWNERID2 4DGT_ITEMEFFORTDATA_DAILYHCLT_IDX_DGT_IFDOWNERTYPE1 There is no index on DGT_ITEMEFFORTDATA_TEMP table
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.
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'.
I have a query optimized as to it indexes and others runs immediately when the answer is few records in SQL Server such as Oracle, however when the result is large eg 20,000 records all data access times are very diferent. The query returns many fields (about 20) and some of them are of type Varchar 250 and some of 2000 I understand here may be the problem, but not is because for similar results (20,000 records) sql run in 2 seconds and Oracle but it responds little to have full data takes around 30 seconds. The problem is really in bringing information to all these fields since if the inquiry it also but only returning a numeric field is done in 2 seconds. Tests I've done them both through ODBC, in the Toad as in the own Oracle console on the server, so it is not problem Driver or flow of data through the network, I would like to think that this is some of the settings I think there is as much difference between Oracle and Sql. The databases are ORACLE 10 and SQL Server 2008.
I wish to run a SQL query and measure elapsed time, then compare the values to other Oracle DBs from other companies. That will give me a feeling if our DB performs well.For example in UNIX world, you can create a random 4GB file to measure throughput I/O and compare the values (for example 4MB/sec).
What's the simplest way to compare DB response time from forum members to our own DB? I don't need 100% accurate numbers.
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
