I'm very happy about materialize hint and I use it a lot. But is it possible to set some hint indicating that I want to materialize some inline view results (temp table transformation) as IOT (index organized table)?
IOT must have primary key. Ok, could it be all the columns in listing order? That would convenient. I know I can use global temporary with index, but this forces me to split one statement into parts.
Can we use Materialized views in Streams like how we use tables... ?
View 11 Replies View RelatedI am not able to create materialized view with 'Refresh Fast on Commit' when the table is residing on the another user environment, I am not able to find the reason why?
You can re-generate the scenario at your end with the following code-
--Login to System-
create user manu identified by manu;
grant connect, resource to manu;
create user arpit identified by arpit;
grant connect, resource to arpit;
grant create materialized view to manu;
[code]....
but when I am doing it with on demand options, materialized view is being created-
--Login to Manu-
create materialized view arpit_view
build immediate
refresh on demand
as
select * from arpit.arpit
/
I've got SQL code generated by an HP tool to make an update of itself. This code is not modifiable because was execute 'in background' from the tool updater.
The macro operations made by the updater (through sql code) are:
--create a copy of actual table and rename with '_old' suffix
--create new table, with indexes and constraints
--insert data into new table from old tables
Now, only in one table, when the data were inserted, an ORA-00001: unique constraint (ASSET_SVIL.CFG_CFGSECTIONCFGE) violated, appear.
To insert data is used an insert/select statement with an HINT /* APPEND */. We verify all the data existing in the old table (that is the data to 'migrate') but there aren't duplicate record!
BUT IF WE REMOVE THE HINT /* APPEND */, THE INSERT/SELECT STATEMENT WORKS WITHOUT GENERATE ERRORS!!
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 RelatedI 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?
I have been asked to rewrite the following update statement without using the hint BYPASS_UJVC.
l_new_CFT_ID CASHFLOW_TYPE.CFT_ID%TYPE;
if (l_Record > 0) then
-- since at least 1 loan was found with the old type, process the actual update
update /*+BYPASS_UJVC*/ (
select
cfa.CFA_CFT_ID
[code]......
I think I am supposed to be using the Merge statement but I am not sure on how to go about it.
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 /*+ FULL(COMP_TM) FULL(TRANS_TM) FULL(INVC_TM) */
CUST_BE_ID ,
DISTR_BE_ID ,
FG_BE_ID ,
KIT_BE_ID ,
BG_ID_NO_BE_ID ,
[code]....
The statistics gathering activity of FACT_DLY_ALGND_SLS table takes around 5 hours to complete. It is a range partitioned table with subpartitions.
I am trying to understand "ordered" hint. I want to use it in my sql where I am using some left outer joins. I believe if I use this ordered hint it will be much faster.
But the problem is I am not able to understand the concept of this hint. As i did alot of search on this eveyone says it will be , even I got some similar references where it worked But I am getting contradicting explanation for on this.
I am writing below MERGE statement. In this cardinality between table_a and table_b is 1:2. I.e. each record in table_b corresponds to 2 records in table_a based on columns in ON clause.
Well this query throws below error.
----Error---
ORA-12801: error signaled in parallel query server P011
ORA-30926: unable to get a stable set of rows in the source tables
However, the same statement executes successfully when PARALLEL hint is removed altogether. (There are no duplicates in table_b based on unit,group,loc columns.)
-----Query--------
MERGE /*+ PARALLEL(8) */
INTO table_a a
USING table_b b
ON (a.unit = b.unit AND a.group = b.group AND a.loc = b.loc)
[Code]....
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 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?
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
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%'
does parallel hint in query can create bottleneck/Slowdown/crash database..??
View 4 Replies View RelatedI am having doubt on the no_index concept in oracle. I am using oracle exadata server.It is basically data warehouse project.I am in the situation to join some tables and get the result set for reporting purpose.
Among the tables, 2 tables having huge count.1st table has more than - 1,000,000,000 rows2nd table has more than - 200,000,000 rows when i join these 2 tables with some small set of tables, it is taking long time (around 20 MIN) to retrieve the result set. The final result set is around 100 rows only.
But, when i force NO_INDEX hint in the same query, is giving the same result in very fast manner(around 5 MIN). Because it is working based on cell smart scan. So, can i force the NO_INDEX hint to all tables? I forced the NO_INDEX hint only the table which is contain 200,000,000 rows not for others.
Query Plan : Normal Query using the the range scan based on the key. No_INDEX Query going full table scan.
