SQL & PL/SQL :: Different Plans On Select Query?
Dec 6, 2012
We're using Oracle 10g for development purposes.I have 2 same schemas with approximately same data.I'm running same query on 2 schemas and I see that the first schema runs the query around 20sec and the 2.schema less than 1 sec. I thoughtfirst that there may be missing constraints or indexes but all are the same.I checked the plan for the 2 schemas and I see that the plan is different.
Here is the query:
SELECT ccc.ComponentId AS "ComponentId", ccp.Code AS "ParentCode", ccc.Code AS "ChildCode" FROM CatalogueComponent ccp INNER JOIN CatalogueComponent ccc ON ccp.ComponentId = ccc.ParentComponentId WHERE ccc.ComponentId IN (20934777, 1594747)
I'm sending also the 2 output of PLAN results from 2 different schemas.
View 19 Replies
ADVERTISEMENT
Dec 6, 2012
We're using Oracle 10g for development purposes. I have 2 same schema with approximately same data. I'm running same query on 2 schemas and I see that the first schema runs the query around 20sec and the 2.schema less than 1 sec. I thought first that there may be missing constraints or indexes but all are the same.
I checked the plan for the 2 schemas and I see that the plan is different.
Here is the query:
SELECT ccc.ComponentId AS "ComponentId", ccp.Code AS "ParentCode", ccc.Code AS "ChildCode" FROM CatalogueComponent ccp INNER JOIN CatalogueComponent ccc ON ccp.ComponentId = ccc.ParentComponentId WHERE ccc.ComponentId IN (20934777, 1594747)
I'm sending also the 2 output of PLAN results from 2 different schemas.
what should I do to fix the problem with the NAFBCA schema.
Attached File(s)
NAFBCA.JPG ( 57.49K )
Number of downloads: 6
NAFBCA2.JPG ( 60.36K )
Number of downloads: 4
View 1 Replies
View Related
Aug 20, 2012
[URL]...
I have a quite complex view that selects from approx 10 long tables (approx 4M records each) and build one "customer sentence" pre customer id. I will be always getting just one row from this view, eg. select * from my_view where party_id = XYZ. I'll NEVER EVER select the whole view.
The problem is that running a query:
select * from my_view where party_id = XYZ takes really long time, while putting the party_id = XYZ condition directly into the view executes in 0.0 seconds.
After putting a ORDERED FIRST_ROWS(1) hint into a view the execution plans seems to be the same (or very similar) for both queries. Unfortunately, I can not transfer anything but screenshot from the environmnet - therefore I paste the exec plans as screenshots only - pls follow the link: [URL]...
View DDL:
create or replace view my_view as
select /*+ ORDERED FIRST_ROWS(1) */ pt.party_id
pt.party_id as id_klienta_mdm,
pt.master_reference_no as id_klienta_ref_mdm,
[code]...
View 6 Replies
View Related
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
Aug 7, 2009
I am looking to simplify the below query,
DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)
Since both the inner queries are same,I want to extract out to a local variable and then use it.
Say,
Array var = SELECT ID FROM B WHERE BID=0;
And then ,
DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)
How to do this using SQLPLUS?
View 8 Replies
View Related
Jan 14, 2013
DB=10.2.0.5
OS=RHEL 3
Im not sure of this, but seeing different plans for same SQL.
select sql_text from v$sqlarea where sql_id='92mb4z83fg4st'; <---TOP SQL from AWR
SELECT /*+ OPAQUE_TRANSFORM */ "ENDUSERID","LASTLOGINATTEMPTTIMESTAMP","LOGINSOURCECD","LOGINSUCCESSFLG",
"ENDUSERLOGINATTEMPTHISTORYID","VERSION_NUM","CREATEDATE"
FROM "BOMB"."ENDUSERLOGINATTEMPTHISTORY" "ENDUSERLOGINATTEMPTHISTORY";
[code]....
View 5 Replies
View Related
Mar 10, 2012
Is there a technique to getting a Top-N query to work as a sub-select in a larger query -or- is there another way to generate Top-N like results that works as a sub-select?
Background:
We have a large query that is being used to build an export from a legacy HR system to a new one. Amount the data needed in the export is the employees primary phone number.
The legacy HR system allows multiple phone numbers to be stored in a simple table structure:
SELECT emp_id, phone_type, phone_number
FROM employee_phones
emp_idphone_typephone_number
------- --------------- -------------------
46021CELL2222222222
46021HOME1111111111
46021WORK3333333333
The new HR system does allow for multiple phone numbers, however they need a primary phone number identified and stored with the employee master information. (Subsequent phone numbers get stored in alternate table.)
From a business perspective, we have decided that if they have a HOME phone in the legacy system that should be the primary in the new system, if no HOME phone, then WORK, if no WORK then CELL.
That can be represented as:
SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')
emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111
46021WORK2222222222
46021CELL3333333333
Or similarly with Top N concept:
SELECT *
FROM (SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1
emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111
Or really what I want in my export:
SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1
phone_number
-------------------
1111111111
However, when the Top-N query is added as a sub-select in a larger query using the employee id from the larger query (WHERE emp_id = export.emp_id), it fails saying that �export.emp_id� is not a valid id.
(SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = export.emp_id
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1)
1.Any way around this? Is it possible to put a Top-N (with a WHERE clause using data from the main query) in a sub-select?
2.Any alternatives (other than Top-N) to delivering a ROWNUM=1 result with a �custom� ORDER BY statement?
Other Notes: Yes, we know we could do two queries in the data conversion first deliver the bulk data to the target table, and then update with the phone numbers. However, for multiple reasons, that is less than desirable.
View 3 Replies
View Related
Nov 1, 2011
Lately, there has been a handful of intermittent performance issues and it all boiled down to the fact that Oracle happened to choose a "bad" plan to execute them for a just a set of bind variables.
I understand Oracle must be doing it for a good reason but it just worked for us when we ensured a single plan is always used. We did it either by creating indexes or sometimes out of no other choice forcing a particular plan (manual plan).
Now, as a proactive measure, I am interested in identifying if there are more such cases. I managed to find about 23 such cases by using the query below. I am planning to regularly check the report generated by the below query.
Select Sql_Id,Count(*) From
(Select Distinct Sql_Id,Plan_Hash_Value From Dba_Hist_Sqlstat where plan_hash_value <> 0 )
Group By Sql_Id
having count(*) > 1
But before we really start addressing them I wanted to know if it is correct to consider that it is always a bad thing to happen if Oracle chooses multiple plans? I am not saying all such cases are giving problems but we did see that a handful of such problems boiled down to this case.
View 6 Replies
View Related
Aug 23, 2012
multiple execution plns for the sql_id from AWR,i have following questions
1.which plan is opimizer tcurrently using ?
2.how to make sure optimizer to pick right plans
SQL> select * from table(dbms_xplan.display_awr('fb0p0xv370vmb')) ;
PLAN_TABLE_OUTPUT
SQL_ID fb0p0xv370vmb
[code]....
View 8 Replies
View Related
Apr 3, 2012
We have a query which makes Oracle behave very strangely. It is a straight-forward join between four tables of about 30.000 rows each, with some simple comparisons and some NOT LIKE:s.
When we run this query, it either takes about 1 second or more than 1.000 seconds to run and return the approximately 5.000 rows of the result. If we run the same query over and over again, it fluctuates back and forth between two different execution plans, apparently at random, 3 times out of 4 selecting the 1.000 second version and 1 time out of 4 the 1 second version.
There are no other connections to the database, the schema is not modified, the data is identical, the query is identical, and the response is identical, but the execution time alternates between 1 second and 1.000 seconds.On the same database instance we have another schema which is identical, but with slightly less data, which is used for development. The 1.000 second run times did not happen in that schema, but only in the test system's database.
Therefore we would REALLY like to understand what happens and why, so that we can avoid triggering this in the future. We could try locking the 1 second execution plan, but then we're afraid of doing the same thing wrong again in the future.
Here are the two execution plans that Oracle switches between, more or less at random:
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
5455 5455 5455 HASH JOIN (cr=15663 pr=10536 pw=0 time=855673 us cost=82273 size=2707430769293 card=14028138701)
79272 79272 79272 TABLE ACCESS FULL GROUPS (cr=1008 pr=0 pw=0 time=22154 us cost=277 size=10693 card=289)
[code]...
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
5455 5455 5455 HASH JOIN (cr=15664 pr=0 pw=0 time=778178696 us cost=30838477 size=741611997206725 card=3842549208325)
375411 375411 375411 TABLE ACCESS FULL GROUP_GROUPS_FLAT (cr=3782 pr=0 pw=0 time=51533 us cost=1029 size=25152738 card=375414)
[code]...
The query:
select g.ucid, a.ucid
from account a, groups g, group_members gm, group_groups_flat ggf
where a.ucid = gm.ucid_member
and gm.ucid_group = ggf.ucid_member
[code]...
And excerpts from the schema:
CREATE TABLE "PDB"."GROUPS"
(
"UCID" VARCHAR2(256 BYTE),
"UNIX_GID" NUMBER(*,0),
[...]
[code]...
View 4 Replies
View Related
Jan 27, 2012
When we use the AUTOTRACE / EXPLAIN PLAN we can see the (estimated) best execution plan the Optimizer found for our SQL Command. Is there a way to display all alternate execution plans the Optimizer has considered ?
View 1 Replies
View Related
Feb 1, 2010
When viewing SQL statements in Top Activity in OEM, I am only able to see the Explain Plan for the most recent statement. Is there a setting to increase the number execution plans kept?
View 1 Replies
View Related
Apr 29, 2011
I have a weird optimizer behaviour on a 10.2.0.4 db.When i add "+0" or "-0" to a number predicate, the optimizer produces 2 differents plans. I dont see why. Here are the statements:
A -First statement with suboptimal plan :
SELECT /*KO*/ TFXPPRODUCT.PRODUCTID,
TFXPPRODUCT.PRODUCTTYPE,
VFUTFIX.Datech,
TFXPPRODUCT.SHORTLABEL,
VFUTFIX.TAUCPN,
VFUTFIX.Tik,
[code]....
View 1 Replies
View Related
Feb 23, 2009
I want to select all the field from the table and want to put alias for only field i m just wondering is there any way to write a query something like
select *, emp as employee
from empMaster
here i want to display all the column but only with emp column i want to put alias.
View 1 Replies
View Related
Jun 7, 2011
I have two tables a and b column names are id and date. Data type of date in a and b are different
table (a)
date id
10-DEC-01 2:08:39 PM 1
10-DEC-01 2:08:39 PM 2
10-JAN-02 10:10:22 PM 3
10-JAN-02 10:10:22 PM 4
10-JAN-02 10:10:22 PM 5
table (b)
date id
10-DEC-01 1
10-DEC-01 2
I need table b like this one
table (b)
date id
10-DEC-01 1
10-DEC-01 2
10-JAN-02 3
10-JAN-02 4
10-JAN-02 5
View 1 Replies
View Related
Jul 22, 2010
SELECT DISTINCT PATIENT_ID , (
SELECT ROWNUM,APPOINTMENT_ID FROM
AppointmentDetailsHistory WHERE APPOINTMENT_STATUS_ID = 2
AND VISIT_TYPE_ID NOT IN (7) AND PATIENT_ID = ADH.PATIENT_ID
[code]...
MY SELECT query failing actually i am trying to convert sql server select into oracle.My sql server query is like this.
SELECT DISTINCT PATIENT_ID , (
SELECT TOP 1 APPOINTMENT_ID FROM
EMRAppointmentDetailsHistory WHERE APPOINTMENT_STATUS_ID = 2
AND VISIT_TYPE_ID NOT IN (7) AND PATIENT_ID = ADH.PATIENT_ID
ORDER BY PATIENT_ID, LAST_UPDATED_DATE ASC) AS FIRST_APPOINTMENT FROM
EMRAppointmentDetailsHistory ADH WHERE ADH.APPOINTMENT_STATUS_ID = 2
AND ADH.VISIT_TYPE_ID NOT IN (7)
View 14 Replies
View Related
Jun 10, 2010
select SQL_CALC_FOUND_ROWS navl.xydata.Lat,
navl.xydata.Longi,
MIN(navl.xydata.GpsTime) as mn,
MAX(navl.xydata.GpsTime) as mx,
timediff(MAX(navl.xydata.GpsTime) ,MIN(navl.xydata.GpsTime) ) as idle,
[code]......
i want to delete WHERE mn = mx .
View 4 Replies
View Related
Jun 7, 2012
How to find display the o/p like manager name under dependent employess same like parent child relation ship on noraml emp table:
sample o/p:
name job
xx manger
yy sales
yy1 sales
aa manager
rr marketing
rr1 marketing
View 6 Replies
View Related
Sep 19, 2013
write a query to get the first row after order by clause using single query alone.Example:I can write following query to select first rowselect * from (selec * from t order by col1) where rownum = 1;But here I should not use inline view to get the result. Because my original requirement needs to use this query in select list and it needs to use a column (of a table from the FROM clause) in the where clause of inline query. Because there is restriction that we can not use the column (of a table from the FROM clause) more than one level of inline query.
View 6 Replies
View Related
Nov 27, 2012
I am using this as a subquery within a large select statement.
(select NAME_LAST from person_name where person_id=enc.person_id and ROWNUM = 1 order by person_name_id desc) as PatFirstName
I am getting issues when i am doing rownum=1 with order by clause, what is teh right way.
when i use rownum < 2 without order y clause it is workign fine.
I would like to use order by clause.
View 2 Replies
View Related
Sep 13, 2004
Can we create a table from a Select query ?
View 5 Replies
View Related
May 10, 2013
I have a Select query which is not fetching records in 11g (11.2.0.2.0) but working fine in 10g (10.2.0.4.0). The query is as below.
--CREATE TABLE t1 (col1 NUMBER, col2 VARCHAR2 (15 CHAR), flag varchar2(1))
--insert into t1(col1, col2, flag) values(1, 'a', 'Y');
--insert into t1(col1, col2, flag) values(2, 'b', 'N');
SELECT *
FROM t1 x
WHERE col1 = 1 AND col2 = 'a' -------------- condition1
AND 0 = -------------- condition2
NVL (
(SELECT COUNT (1)
FROM t1 y
WHERE y.flag = 'N'
AND x.col1 = y.col1
AND x.col2 = y.col2),0)--=0
When remove NVL function or change the condition by having AND NVL(SELECT) =0 the query working fine.
View 9 Replies
View Related
Jun 13, 2007
In sql plus How do I get the number of distinct rows of a certain value?
for example
select group_number from records group by group_number
How would I query for the total number of group_numbers in this query?
View 2 Replies
View Related
Mar 22, 2013
I have a table A on dev with definition as TAble A(address,name) and the same table on Prod is defined as Table A(name,address).
my question is Ihave one package in that am trying to insert into this table as follows:
INSERT INTO A
SELECT b.name name,
a.address address,
[Code]....
so the query works on Prod but fails on Dev because column order is different.
I have 2 solutions:
1. I can mention column names in insert line and modify the query but tomorro some body changes again the definition of table A I need to change the query, so do I have solution in oracle sql that can handle the column order without specifying the column names in insert line.
so tomorrow On prod column order and on Dev column order is different though my sql should successfully execute.
View 5 Replies
View Related
Jul 13, 2010
i have a good query but I thought i know the solution but actually I didn't it's very simple and straight forward but i didn't catch the rope terminal to follow now I have the following code
CREATE TABLE TRANSLATORS
(Tr_code VARCHAR2(8),
Tr_name VARCHAR2(50),
Tr_age number(2),
Tr_location varchar2(25),
constraint PK_TR_CODE PRIMARY KEY(Tr_code)
);
[code]....
NOW I NEED A QUERY which will select the only translator who knows the languages written in where clause like if i specify two languages like 'italy' and 'english' the query should retrieve to me GH and SE also if i passed languages 'spainsh' and 'persia' it should return JH only
but if i passed languages like 'Italy' and 'Dutch' it should not return any thing (just : no rows selected)
View 14 Replies
View Related
Oct 25, 2013
He had created an object type and wanted to populate it in a query. So far so good. But then he wanted to select some attributes from that object in the same query. Basically, he wanted to do something like this:
With his_view
As
( select object_type( attr1, attr2) theobj
From his_table
Where ...
)
Select theobj.attr1
From his_view
But somehow he was hitting ORA-00904: "THEOBJ"."ATTR1": invalid identifier over and over again.Here's a test script:
Create type mhe_type As Object( col1 Number
, col2 Varchar2(30)
)
/
-- Basic select
With mhe_view
[code]...
Specify a correlation name, which is alias for the table, view, materialized view, or subquery for evaluating the query. This alias is required if the select list references any object type attributes or object type methods. link...
View 1 Replies
View Related
Jul 8, 2011
i have a problem in the following query. i need to fetch the rows such that i want to fetch all the records keeping "segment1" column as distinct and sum all of the corresponding "quantities" column.
select prha.segment1 --as requisition_no
,prha.creation_date
,sum(prla.quantity)
,prha.description
[code]...
i tried to use the partition technique. using partition solved the problem apperently. the sum function worked but redundancy in "segment1" column still persists. i used the sum function only to extract the distinct "segment1" column and summing its corresponding "quantity" column (only quantity column differs in the redundant rows...)
the second query was like:
SELECT prha.segment1,
prha.creation_date,
SUM(prla.quantity) OVER(PARTITION BY prha.segment1) AS qty,
prha.DESCRIPTION,
[code]...
View 1 Replies
View Related
Jun 11, 2008
I have a table structure like :-
Create table test(A varchar2(50),B NUMBER);
The data in that table is like that:-
A B
----------------------
2*3
2*4*5
4*5
column B contain no data.
I want to create a function which can be used in a select query,and the output should come like that :-
A B
----------------------
2*3 6
2*4*5 40
4*5 20
Means column B contains the resultant value of column A.And the above output should come through a select statement.You can use any function inside the select statement.
View 20 Replies
View Related
Oct 30, 2013
How does one select * from one table without selecting * from other tables that are included in a query? For example, if in the query below I want to view all fields in some_table, but not the fields from other_table, how do it?
select *
from some_table st,
other_table ot
where st.id = ot.id
View 15 Replies
View Related
Apr 12, 2011
I have created a table using a CTAS
E.g. :
CREATE TABLE temp1 AS
SELECT * FROM table1;
Now after the table got created, is there any place in the data base where the select query is stored using which the table got created? In brief, I would like to get the select query through which the table got created.
View 2 Replies
View Related
