Parallel Query Block Of Union All Set

Sep 24, 2010

We have very large table having data more than 1000 millions rows. We divide this table into four physical tables say A, B, C and D. The physical horizontal partition of data of this original table is done based upon their business policy.

Each partitioned table has contained data of particular business entity. Further each table has partition and sub partitions based upon business rule.

We have to retrieve data from all these tables as follows:

select a1, a2, a3, a4, a5, a6
from A
where < logical filter condition>
union all
select b1, b2, b3, b4, b5, b6
[code].....

We observed that above each query block execute in serial one after another and individual each query block capable to process data in parallel from respective table.

How does this above query able to execute each query block in parallel?

View 14 Replies


ADVERTISEMENT

Reports & Discoverer :: SQL Union All - Query Block Has Incorrect Number Of Result Columns

Feb 1, 2012

I have the following Union All query. It throws the following error in SQL plus

ERROR at line 27: ORA-01789: query block has incorrect number of result columns

After doing some google for the above error it suggests there are incorrect number of columns in the Union All query.I could not figure out the exact location well SQl Plus says error is on line 27 at the first opening bracket like

(Select distinct c.contact_code

Following is the SQL query

Select
tbl_contact.contact_code,
contact_title
||'.'||contact_name contact_name,
contact_address,
[Code] ......

View 1 Replies View Related

SQL & PL/SQL :: Union Run In Parallel

Sep 30, 2010

I have a query which has 5 unions, each clause of the union takes 1 hr to run and query results come back in 5 hrs, Is there any way I can make these clause to run in parallel?

View 13 Replies View Related

SQL & PL/SQL :: Union Query Need To Add Extra Dummy Field To One Side Of Query

Dec 8, 2005

I have inherited a query that union alls 2 select statements, I added a further field to one of the select statements ( a date field). However I need to add another dummy field to the 2nd select statement so the union query marries up I have tried to do this by simply adding a

select
'date_on'
to add a field called date on populated by 'date_on' (the name of the column in the first query)

however when I run the union query i get the error Ora-01790 expression must have same datatype as corresponding expression.

View 6 Replies View Related

SQL & PL/SQL :: Query Without Using A UNION?

Aug 17, 2011

I'm just trying to see if there is another way of doing this query without using a UNION. The only way I can see is using a UNION but maybe I'm missing something or a way to do it without a UNION.

Result:

Select any customer within the user_states table who lives in "MO"
Select any customer within the user_cars table who lives in "MO" and has a "White" car
Select any customer within the user_plates table who lives in "MO" and has a plate of "A" or "B"
Join to the user_names table to display the customers name

So, the result would be any customer who lives in "MO" who owns a "White" car and any customer who lives in "MO" who has a plate of "A" or "B".

SQL

select b.customer_id,
b.first_name,
b.last_name

[Code]....

View 4 Replies View Related

SQL & PL/SQL :: Union Query For Oracle

Feb 29, 2012

I am new to SQL Oracle programming and have a question on a union query. I am trying to get results (example) for the following:

Org # Full_date Run_date
5 2/20/2012 2/20/2012
5 2/21/2012 2/21/2012
5 2/22/2012 null (there is not a record for this)
6 2/20/2012 2/20/2012
6 2/21/2012 null
6 2/22/2012 2/22/2012
7 2/20/2012 2/20/2012
7 2/21/2012 2/21/2012
7 2/22/2012 2/22/2012

The dw_time table would have the listing of all dates, (Full_date) and the dw_capacity_daily table would have the run_date. Here is my

select * from (
select distinct a.Organization_Nbr, d.full_date
from CMBHS_DW.DW_ORGANIZATION a, cmbhs_DW.DW_Organization_Identifier b, cmbhs_DW.DW_Contract c, cmbhs_dw.dw_time d
where a.ORGANIZATION_NBR = b.ORGANIZATION_NBR
[code]....

View 7 Replies View Related

SQL & PL/SQL :: Query Performance In Union

Aug 10, 2010

which one is better?

unloading 5 tables of same structure using a ETL tool then merging the data

using Union operator to unload 5 tables then do transformations in ETL tool

View 4 Replies View Related

SQL & PL/SQL :: Query For Eliminating Union

Apr 24, 2011

I have one scenario in which i want to write the sql,but not able to write correct qry,

tables
1-emp
2-emp_hist
3-dept
4-dept_hist.

i want to retrieve data from the emp and hist able based on some conditions,but if data is not present in emp and dept tables.then fetch data from emp_hist and dept_hist.I have written below qry which is working fine,but my prob is i want to provide my emp_id only one place.
else i have to change my java code.

select * from emp,dept where emp.emp_id=dept.dept_id and emp_id=5
union
select * from emp_hist,dept_hist where emp_hist.emp_id=dept_hist.dept_id and emp_id=5

View 20 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

Parallel Query Option

Oct 23, 2013

Just a general query on parallel query.  My customer having 4 cpus and running the database in 11.2.0.3 in AIX 5.3(One is in AIX 6.1). Under which circumstances, we can propose to user parallel query options.

View 2 Replies View Related

Materialized Views Not Used With UNION ALL In Query

Oct 13, 2010

I am an Oracle newbie.

We have 2 fact tables and one lookup table in this structure:

FACTTABLE1 (C1_ID, C2, SALE)
FACTTABLE2 (C1_ID, C3, SALE)
LOOKUPTABLE (C1_ID, C1_NAME)

The DBAs have built 2 Materialized Views, which aggregates data in the fact tables at column C1 level

MAT_VIEW1 :SELECT C1_ID, SUM(SALE) SALES from FACTTABLE1 join LOOKUPTABLE on C1_ID
MAT_VIEW2: SELECT C1_ID, SUM(SALE) SALE from FACTTABLE2 join LOOKUPTABLE on C1_ID

We are using an old BI tool that can ONLY generate Inline Views in these formats.

CASE1:
select
INL_VIEW.C1_ID
,LOOKUPTABLE.C1_NAME
,sum(SALE) SALE
from
(select C1_ID, C2_ID, null C3_ID, SALE from FACTTABLE1)INL_VIEW
join LOOKUPTABLE
on INL_VIEW.C1_ID = LOOKUPTABLE.C1_ID
group by INL_VIEW.C1_ID, LOOKUPTABLE.C1_NAME

CASE2:
select
INL_VIEW.C1_ID
,LOOKUPTABLE.C1_NAME
,sum(SALE) SALE
from
(select C1_ID, null C2_ID, C3_ID, SALE from FACTTABLE2)INL_VIEW
join LOOKUPTABLE
on INL_VIEW.C1_ID = LOOKUPTABLE.C1_ID
group by INL_VIEW.C1_ID, LOOKUPTABLE.C1_NAME

CASE3:
select
INL_VIEW.C1_ID
,LOOKUPTABLE.C1_NAME
,sum(SALE) SALE
from
(
select C1_ID, C2_ID, null C3_ID, SALE from FACTTABLE1
union all
select C1_ID, C2_ID, null C3_ID, SALE from FACTTABLE2
)INL_VIEW
join LOOKUPTABLE
on INL_VIEW.C1_ID = LOOKUPTABLE.C1_ID
group by INL_VIEW.C1_ID, LOOKUPTABLE.C1_NAME

Oracle 11g rewrites Case 1 and Case 2 to use the correct materialized views. But for case 3, it goes to the base fact tables 1 and 2. Is there a way to make oracle use the MVs even if there is a UNION ALL in the inline view? There is a 1:M Foreign Key relationship between LOOKUPTABLE.C1_ID and the 2 fact tables.

View 2 Replies View Related

SQL & PL/SQL :: Improve Query So That UNION Can Be Removed

Mar 6, 2012

select
a.empno,a.ename,a.job,
B.DNAME
from scott.emp a,scott.dept b
where ( a.ename like 'S%' and a.deptno=b.deptno)
union
select
a.empno,a.ename,a.job,
'aaa' AS DNAME
from scott.emp a,scott.dept b
where ( a.ename like 'S%' and a.job not like 'SALES%');

Output:

7369SMITHCLERKRESEARCH
7369SMITHCLERKaaa
7788SCOTTANALYSTRESEARCH
7788SCOTTANALYSTaaa

Quote:There is other way to improve the query so that UNION can be removed.

View 3 Replies View Related

SQL & PL/SQL :: Query Regarding Parallel Hint Degree?

Jan 3, 2012

I have been told that i should use multiple's of 4 as degree in the parallel hint to get maximum performance, so i am wondering is it true? that i should always use multiples of 4 or i can use any number inside the parallel hint.

View 4 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

Reports & Discoverer :: How To Add Union To Report Query

Feb 16, 2012

I am making a report in hrd regarding gross, deduction and there difference. I hv an attribute in the table as indicator whose value is addition and deduction. i want the sum of both in two diff column in a single rep. i want the report dept wise.

but i m getting fatal error at run time. which i don't understand.

View 3 Replies View Related

RAC & Failsafe :: Query Doesn't Have Any Parallel Hints

Jul 10, 2013

Below query doesn't have any parallel hints. Though, it is without where clause, so full scan is must but why "PX Deq Credit: send blkd" occurred?

SELECT ROWID, REGION, STATE, CITY, DEALERNAME, DEALERCODE, DEALERID, BUSINESSUNIT, GARDEALERID, MASTERCLAIMNUMBER, CLAIMNUMBER, COMPANY, POLICYNUMBER, STARTDATE, ENDDATE, POLICYISSUEDEALERNAME, ISSUEDEALERCODE, CUSTOMERNAME, CUSTOMERADDRESS, ACCIDENTDATE, ACCIDENTTIME, INTIMATIONDATE, INTIMATIONTIME, REGISTRATIONDATE, REGISTRATIONTIME, DRIVERNAME, ACCIDENT_DESCRIPTION, INTIMATION_DELAY_REASON, INTIMATORNAME, INTIMATORMOBILENO, CLAIM_INTIMATED_BY, TOWING_ASSISTANCE, ACCIDENTPLACE, ACCIDENTLOCATION, GARAGENAME, WORKSHOPADDRESS, WORKSHOPMOBILENO, MANUFACTURINGYEAR, CHASSISNUMBER, ENGINENUMBER, REGISTRATIONNUMBER, VEHICLEMODEL, BUSINESSTYPE, TOTALSI, CLAIMEDAMOUNT, ESTIMATEDAMOUNT, "LABOUR(E)", "PARTS(E)", "PAINTING(E)", EXCESS,
[code]....

understand about "PX Deq Credit: send blkd"..

View 1 Replies View Related

SQL & PL/SQL :: ORA-12827 / Insufficient Parallel Query Slaves Available

Nov 25, 2010

We are facing the below error in our Production environment.

ORA-12827: insufficient parallel query slaves available

View 6 Replies View Related

Parallel Query ORA-01002 - Fetch Out Of Sequence

Feb 16, 2013

While executing parallel query we are getting the below errors.

(SQLState = HY010) - java.sql.SQLException: [tibcosoftwareinc][Oracle JDBC Driver][Oracle]ORA-01002: fetch out of sequence"
(SQLState = HY000) - java.sql.SQLException: [tibcosoftwareinc][Oracle JDBC Driver][Oracle]ORA-12842: Cursor invalidated during parallel execution

Database Version: Release 11.2.0.1.0 (Non-RAC)

SQL Query:
SELECT /*+ PARALLEL 4 */
DISTINCT
AI.STORE_ID STORE_ID, AI.DUE_DATE DUE_DATE, AI.INSTRUCTION_ID INSTRUCTION_ID,
DECODE (ASR.ADT_INSTR_KEY, NULL, 'F', ASR.ADT_INSTR_KEY, 'S') QCCHECK
FROM NSOAPP.DA_INSTRUCTION_INFO AI,
[code]....

View 4 Replies View Related

Performance Tuning :: Parallel Index With Non-partitioned No-parallel Table?

Apr 30, 2012

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

View 5 Replies View Related

SQL & PL/SQL :: ORA-12805 - Parallel Query Server Died Unexpectedly?

May 24, 2010

ORA-12805: parallel query server died unexpectedly

View 4 Replies View Related

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?

View 1 Replies View Related

Performance Tuning :: Select Query Taking Time Even After Using Parallel Hint?

Sep 25, 2013

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

[code]....

Explain plan output:

Plan hash value: 2779236890
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name| Rows| Bytes | Cost (%CPU)| Time| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT || 1 | 29 | 9633M (8)|999:59:59 |||
|* 1 | FILTER |||| ||||
| 2 | PARTITION RANGE ALL || 738M| 19G| 6321K (1)| 21:04:17 | 1 | 6821 |

[code]....

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (<not feasible>)
4 - filter("PRODUCTID"=:B1)
5 - filter(ROWNUM<100)
12 - access("MODELID"=:B1)

[code]....

Note: - SQL profile "SYS_SQLPROF_014153616b850002" used for this statement

View 2 Replies View Related

Performance Tuning :: Does Parallel Hint In Query Can Create Bottleneck / Slowdown / Crash Database

Oct 20, 2011

does parallel hint in query can create bottleneck/Slowdown/crash database..??

View 4 Replies View Related

SQL & PL/SQL :: Is It Better To Use UNION (or) UNION ALL With DISTINCT

Apr 22, 2010

Is it better to use UNION (or) UNION ALL with DISTINCT,Which one will improve performance.

Query1:

SELECT deptno FROM emp
UNION
SELECT deptno FROM dept

Query2:

SELECT DISTINCT * FROM(SELECT deptno FROM emp
UNION ALL
SELECT deptno FROM dept)

I mean to say query1 is better (or) query2 is better,which query improves the performance.

View 1 Replies View Related

Forms :: Data Block Using Query?

Mar 1, 2010

I wanna to load data in a data block using sql which using multiple table, views based on condition

View 7 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

Forms :: Clear A Block For Enter Query Mode?

Jan 16, 2012

I am using Forms 6i,Oracle 10g. In a form I created, I have 3 blocks, first is a toolbar type with buttons to save, exit etc., other two are one master and detail data block.

how to get the default Oracle toolbar with the buttons, save, delete etc. I dont get in my form.

When the cursor is inside the detail block and then I click on an EnterQuery button in the first block, how do I clear the detail block, using the on btn press code.

What happens when I click the Enter Query button, is that the cursor and current block becomes the button block and when I run the command ENTER_QUERY, no use.

I need to set the block in which the cursor is to be cleared for query, whether it is master or detail, by pressing the button on the first block.

View 7 Replies View Related

Performance Tuning :: How Many Block Of Query Is Fetching With Or Without Indexes

Jun 1, 2012

I don't have any dba privileges, can you share a scripts which can tell how many block my query is fetching with or without indexes. How do i also get buffer hit, how can i get i/o without sql trace as i don't have access to dump_dest

I have a below query

SELECT DISTINCT ser_id AS STA_ser_id, rct_name AS STA_name
FROM sd_servicecalls, rep_codes, rep_codes_text
WHERE ser_sta_oid = rcd_oid
AND rcd_oid = rct_rcd_oid
AND rct_name IN ('New', 'Awaiting Approval', 'Approved', 'In Progress', 'Awaiting Supplier', 'Awaiting RFC', 'Awaiting Release', 'Pending Release', 'On Hold', 'Resolved', 'Implemented', 'Closed');

Does large hash value in explain plan mean more resource needed and more time to execute the query, How can i use ADDM for the above sql.

View 7 Replies View Related

Forms :: Enter-Query Mode - One Item In A Block Is Not Getting Cleared

May 24, 2010

I have a form in which there are three blocks A,B and C. When I press F7, the form enters Enter-Query Mode and all blocks clear except block A's one item. Say, Block A has 5 items, Item1, Item2,.. Item5. When I enter Enter-Query mode, Item2 of block A is not cleared and rest all are cleared.

View 2 Replies View Related

Forms :: Query Records Of Detail Block Displayed In Single Row

May 30, 2010

I have a Master block and a Detail Block. They are related using two columns, Document_ID and Page_no. I want to display all the records in master table with corresponding detail records beside them in single line. i.e., as shown below:

MasterItem1 DetailItem1 DetailItem2 DetailItem3

I created the relation between them and executed query. When I execute query in the form, I can find that all the Master Items are displayed in vertical records, but i can only see one record of detail fields. They are displayed as shown below:

mitem1-value1 ditem1value1 ditem1value2 ditem1value3
mitem1-value2
mitem1-value3
mitem1-value4

When the navigate down in master items, then the corresponding detail values are being shown on the same first record.

View 1 Replies View Related







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