How To Make Index Use In Query Execution

Jun 6, 2013

I have the below query for which ename column has an index. As of my knowledge below queries 1st and 2st will not use index. Hence i used the 3rd statement and that too its not using the index. Finally i used the 4th query, but even the 4th query is not using the index. Then how do i make this query to use my index??? Do i need to create a function based index for this?

1. select * from emp where ename !='BH' ;
2. select * from emp where ename <> 'BH';
3. select * from emp where ename not in ('BH');
4. select * from emp where ename < 'BH' or ename > 'BH';

View 4 Replies


ADVERTISEMENT

PL/SQL :: Make Index On Column?

Oct 5, 2013

If my query is under execution and I want to make an index on a column which is very much needed by my query. Will a simple index solve the purpose or is there any extra keyword required ?

View 2 Replies View Related

SQL & PL/SQL :: How To Make IS NOT NULL Condition To Use Index

Aug 23, 2013

The below query is not performing good.

SELECT * FROM wedb_master
WHERE c_group IS NOT NULL;

The table is having 200000 records.

I have index on c_group but not used. FTS is happening.

how improve the performance of IS NOT NULL predicate.

View 2 Replies View Related

Performance Tuning :: Lost Index From Second Execution Time?

Jul 11, 2013

The problem was describe:

- First time to execute: Using all indexes on 2 tables

- Second time to execute: Using only indexes on first table, full table scan on the other

- Third time to execute: Do FTS on both of tables.

Now, I show the objects and relate information here:

The Tables:

system@dbwap> select count(*) from my_wap.news_relation;

COUNT(*)
----------
272708

system@dbwap> select count(*) from my_wap.news_content;

COUNT(*)
----------
95092

system@dbwap> desc my_wap.news_content;
Name Null? Type
----------------------------------------------------- -------- ----------------
ID NOT NULL NUMBER(11)
SUBJECT NOT NULL VARCHAR2(500)
TITLE VARCHAR2(4000)
STATE NUMBER(1)
IMGPATH VARCHAR2(500)
ALIGN VARCHAR2(10)

[Code]....

View 7 Replies View Related

Performance Tuning :: Index With NVL / Query Is No Longer Using Index

Nov 19, 2010

I 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?

View 2 Replies View Related

SQL & PL/SQL :: Execution Time Of A Query

Mar 16, 2010

I want to know that is there any way to know the execution time of a sql query.

View 1 Replies View Related

SQL & PL/SQL :: Different Result On Every Execution Of Query?

Jan 13, 2011

when I am executing the below query getting diffrent count every time and not able to guess what is happening.

SELECT
count(1)
FROM table1 where table1.LAST_UPDATE_DATE >= current_date - interval '9' day

View 6 Replies View Related

PL/SQL :: Query Execution On Where Clause

Oct 22, 2013

I have the where cluase as below , I would like to know how does oracle decides which one to execute first,  

WHERE S.PERSPECTIVE='S'and s.shipment_gid=sb.shipment_gid AND SB.BILL_GID=CBIL.INVOICE_GIDand inv.invoice_gid=cbil.invoice_gid AND S.SOURCE_LOCATION_GID=LC.LOCATION_GID and l.location_gid=lc.location_gid AND TRUNC(cbil.insert_date)=TRUNC(tc.tesco_cal_date)AND(lc.location_gid='N' OR lc.corporation_gid='TESCO.10719')AND s.source_location_gid=lc.location_gid AND tc.tesco_year='2013' AND tc.tesco_period=6AND tc.tesco_week_number=23     

View 9 Replies View Related

How To Bypass Manual Query Execution

Apr 13, 2013

when i run a form no information shows up until i click execute query... i need the info to be their automatically to browse with the previous and next button

View 3 Replies View Related

SQL & PL/SQL :: Know Execution Time Without Running Query?

Jul 13, 2008

Is there a way to know how much time will a query take to execute without running it, just like using the autotrace (traceonly) and explain plan utility.

View 16 Replies View Related

Unusual High Query Execution Time?

Aug 25, 2010

I am facing a very strange issue with one of our Oracle query. The query is usually completes in a minute or two. Even the execution plan of the query is good and it works perfect most of the times, as expected. The query fetches about 1000-2000 records each day.

But on a given day, the query takes about 30-40 mins to execute completely. Upon checking the load on DB server, there are no other processes running which can impact the run time of this query. Moreover, the record counts fetched are almost same as compared to other days. There is no pattern observed as that this phenomenon occurs. it all happens once in a while.

Configuration is Oracle 10g with RAC environment on LINUX

View 5 Replies View Related

Performance Tuning :: Same Query Using Different Execution Plans?

Dec 26, 2011

I am executing the query below:

INSERT INTO temp_vendor(vendor_record_seq_no,checksum,rownumber,transaction_type,iu_flag)
SELECT /*+ USE_NL ( vd1 ,vd2 ,vd3 ) leading ( vd1 ,vd2 ,vd3 , tvd) */ vd1.vendor_record_seq_no, tvr.checksum, tvr.rownumber, tvr.transaction_type, 'U'
FROM vendor_data vd1,
vendor_data vd2,
vendor_data vd3,
(SELECT rownumber,

[code]....

It is taking different approaches (execution plans) while executing for same set of parameters. Due to which sometimes it executes successfully, but sometimes it fills all TEMP space and get failed. I am pasting both the execution plan (different from expalin plan) below:

I. Successfull Execution Plan:

------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 65612 (100)| | | |
|* 1 | HASH JOIN | | 1 | 6121 | 65612 (1)| 00:13:08 | | |

[code]....

II. Failed with TEMP space Execution Plan:

--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 1967 (100)| | | |
|* 1 | FILTER | | | | | | | |
| 2 | SORT GROUP BY | | 1 | 8233 | 1967 (3)| 00:00:24 | | |
|* 3 | HASH JOIN | | 1 | 8233 | 1966 (3)| 00:00:24 | | |

[code]....

View 8 Replies View Related

SQL & PL/SQL :: Possible To Make Query With Analytic Function

Nov 4, 2011

Is it possible to make query with analytic function or somehow to represent output data for next example (sql: ???):

Table: Order
sql: select * from order
Sql output>
Item Barcode Qty
---- ------- ---
1 100100 2
2 100200 1
3 100300 3

Table: Order
sql: ???
Sql output>
Item Barcode Qty ElementarQty
---- ------- --- ---
1 100100 2 1
1 100100 2 1
2 100200 1 1
3 100300 3 1
3 100300 3 1
3 100300 3 1

View 2 Replies View Related

Forms :: How To Make Block Query

Jan 30, 2012

I have one form, when i press f7 , it says the "The function has no meaning in this context" , how to make it query enabled block.Actually i am going to that block using when button pressed and execute_query.

View 12 Replies View Related

SQL & PL/SQL :: Make Query Like Separate List Of Orders

Mar 1, 2010

I have table called Orders with lots of fields from Imports Fields are ConsigneeKey,Shipto,ProductCode. and another table called Product. Main fields are ProductCode,Price,Weight,StdCube. Now I have two list of Consineekey,One which is shipto X area and another is of Y area. I want to make query like separate list of Orders which is shipto X,Y and Common Products in Both X and Y. I will supply list of Consigneekey in where clause.

View 8 Replies View Related

Forms :: How To Make Query Mode Work (f11)

Apr 2, 2012

I have created a custom form. I have field called Order No. When i query on Order no.. i should be able to pick order no as well as other fields related to it. how do i acheive this functionality. I mean the f11 and ctrl f11 querying functionality.

View 18 Replies View Related

SQL & PL/SQL :: Improve Procedure Which Is Looping Through Query To Make Inserts

Oct 10, 2013

I am trying to improve a procedure which is looping through a query to make inserts.

FOR P IN (
SELECT O.TYPEID
,o.KEY
,O.ID
,O.NAME
,O.LGNUM
,O.LGNAME
[code]....

View 12 Replies View Related

Reports & Discoverer :: How To Make A Query In Data Model

Jul 6, 2013

I have one report consist of two user parameters like FROMDATAE and TODATE and two queries in data model..

The 1st query is..

SELECT WONO,MCV_DATE,QTY FROM MCSHOP1 WHERE MCV_DATE BETWEEN :FROMDATE AND :TODATE;

It created two user parameters.ie FROMDATE and TODATE.

And 2nd query like this

SELECT MCVN FROM MCSHOP1 WHERE WONO=:WONO OR WONO LIKE 'RW%'||:WONO;

I dont know how to make 2nd query in data model. becoz the WONO will come from 1st query and LIKE command is there..But I tried in formula Column....but it returns more than one row...

View 4 Replies View Related

PL/SQL :: Procedure To Make Minus Query As Output For 2 Tables

Nov 30, 2012

create a procedure so that I could get minus query of 2 table as a result.

I have two table 1- src_table_list ,2- tgt_table_list both tables have 2 columns : serial_no,table_name and 100 records each. and details mentioned in column "table_name" are actually tables name which present in my testing database.

so I need one procedure which will pick one table_name from src_table_list and one table_name from tgt_table_name each time recursively and provide minus query as a result. as below.

select c1,c2,c3,c4 from table1 --(fetched from src_table_list)
minus
select b1,b2,b3,b4 from table2 --(fetched from tgt_table_list)

create the procedure..as I have to prepare minus query for more than 200 tables and then I need to test them for integration testing..

View 11 Replies View Related

PL/SQL :: DML Query Execution - Length Exceeds 4000 Characters With Execute Immediate Statement

Jun 18, 2013

I want to execute a DML query with execute immediate statement. That DML query length exceeds 4000 characters. This query has Xquery related conditions, i can not split the query. when i tried execute it is giving "string literal too long".  I tried with DBMS_SQL.Parse() and DBMS_SQL.Execute also, but it is giving same error. I have to execute this DML query inside a Procedure. We are using oracle 10g version

View 13 Replies View Related

Performance Tuning :: Query Taking High CPU And Execution Time In Oracle 11gR2

Dec 24, 2012

The below query is taking high CPU almost 98% and longer time to execute.

SELECT ancestor,
Max(D.alarmstate) ALARMSTATE,
Max(D.sialarmstate) SIALARMSTATE,
Max(D.uncralarmstate) UNCRALARMSTATE,
Max(M.commstate) COMMSTATE,
Max(M.nncommstate) NNCOMMSTATE,
Max(M.servicestate) SERVICESTATE,
Max(M.abnormal) ABNORMAL,
CASE
[code]....

View 15 Replies View Related

Query Not Using Index?

Jul 18, 2012

Query
SELECT case.case_objid FROM clrods.case@clrods.equant.com case, table_x_cwp_tickect_details_vw t WHERE CASE.case_condition_cd IN ('OPEN', 'OPEN-DISPATCH', 'OPEN-REJECT', 'OPEN-RETURNED') AND case.case_type_cd in ('CUSTOMER FAULT', 'CHRONIC','SCHEDULED ACTIVITY','PROBLEM') AND ROWNUM <= 500 AND case.case_objid = t.ticket_objid AND ( ( case.account_id = '672286' ) ) ORDER BY case.case_id DESC

From PROD

Plan
SELECT STATEMENT HINT: FIRST_ROWS Cost: 2,629 Bytes: 221,500 Cardinality: 500
     37 SORT ORDER BY Cost: 2,629 Bytes: 221,500 Cardinality: 500
          36 COUNT STOPKEY
            
[code]...

From DEV platform

Plan
SELECT STATEMENT HINT: FIRST_ROWS Cost: 591 Bytes: 61,134 Cardinality: 138
     37 SORT ORDER BY Cost: 591 Bytes: 61,134 Cardinality: 138
          36 COUNT STOPKEY
        
[code]...

1. Index is rebuild
2. stats are up to date
3. Redef on table is done.

View 7 Replies View Related

Get A Query To Run In Parallel Using Index

Jun 21, 2012

oracle: 10.2.0.5.7...I can get this to work, but not the way the docs seem to say. I am wondering if I am reading the docs wrong or missing something.

The docs seem to say to get a query to run in parallel using an index you use the PARALLEL_INDEX hint. This doesn't seem to work for me. I have to do one of the following

1. change the parallel degree with an alter index, then use the PARALLEL hint (parallel index hint does nothing in this case)
2. use both the parallel_index and parallel hint

View 4 Replies View Related

Pivot Query In Oracle To Get Years From Column And Make Separate Column For Each

Jul 22, 2009

I'm trying to do a pivot query in oracle to get the years from a column and make a separate column for each. I found an example of the code to use on the internet and i changed it for my own tables but i'm getting errors. Namely a "FROM keyword not where expected" error at the beginning of the 'avg(...' statements.

I have copied the code used in

select stud_id, 2006, 2007, 2008, 2009
from (
select stud_id,
avg(case when year=2006 then ((present/poss)*100) else null end) 2006,
avg(case when year=2007 then ((present/poss)*100) else null end) 2007,
avg(case when year=2008 then ((present/poss)*100) else null end) 2008,
avg(case when year=2009 then ((present/poss)*100) else null end) 2009
from attendance.vw_all_attendance_perc
group by stud_id
);

View 11 Replies View Related

Query On Index Move Into Particular Tablespace?

Sep 3, 2010

I have already created large no of indexes on my Database without specifying specific tablespace, now I would like to move all the created indexes into particular tablespace.

View 3 Replies View Related

Query To Find Out Whether Exactly 2 Columns Are Used In Any Index

Dec 17, 2012

Suppose i have two columns ID and Status (or any number of columns ) belongs to table customer. Now I want to create below index

Ex. create index test1 on customer (ID , Status )

But before creating this index i want to check that whether there is already index on these 2 columns ? May be by other name ?

View 23 Replies View Related

Use Oracle Hint To Use Index In 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.

How can I optimize this query further?

Is there a way to pass Oracle Hints so that Oracle uses indexes?

View 4 Replies View Related

Bad Query Performance With Global Non-Partitioned Index?

Aug 17, 2010

I have a partioned (by row_create_date) table, lets called it TABLE_X, which has about 300 million records. This table has 7 columns including the primary key and a non-unique, locally partitioned column called trace_id; 99% of queries access this table via this column.

Lately, querying TABLE_X via the trace_id has been very very bad. Queries run for over 1 hr in some cases. So we decided to change the index for trace_id to a global index. Now queries against TABLE_X return in seconds. So far so good.

However, when the query has to join TABLE_X to another table, the query sometimes runs for over 1 hours; back to the same old problem. Here is an illustration;

SELECT COUNT(1) FROM TABLE_X WHERE TRACE_ID = 'XXXXX';
-- returns in seconds
SELECT COUNT(1)
FROM TABLE_X,
TABLE_Y
WHERE TABLE_X.TRACE_ID = 'XXXXX'
AND TABLE_X.TRACE_D = TABLE_Y.TRACE_ID;

-- runs for over 1 hr, even when TABLE_Y.TRACE_ID is a primary key.

View 3 Replies View Related

Text :: Query To Determine Index Fragmentation?

Mar 27, 2013

Is this the right query to determine index fragmentation ?

SELECT AVG (tfrag)
FROM (SELECT /*+ ORDERED USE_NL(i) INDEX(i DR$TEXT_IDX$X) */
i.token_text,
(1

[code]...

The reason I am asking that before index rebuild it returned 86% but after rebuild (ALTER INDEX .. REBUILD) it returned
96% which does not make sense.

I did try ctx_report.index_stats but it takes more time to run.

View 5 Replies View Related

Check Index Used In Oracle Select Query?

Mar 21, 2013

I have partitioned an oracle table and created Local Index for the Partitioned table .

Now i want to make sure that the Local index is being used when i perform select query on the Table partition.

How do i confirm that ? can i check the explain plan generted for the select query to confirm local index is being used ?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved