finally add a HINT /*+ gather_plan_statistics push_pred(TABLE) */ and the result was not very good in this case, but improved the resolution of the view, the rest got worse
SQL Statement from editor:
SELECT /*+ gather_plan_statistics push_pred(o10475761) */ COUNT(o10475761.SUC_ID)
FROM GAR_DW.ARTICULOS o10475528,
GAR_DW.EMPRESAS o10475602,
GAR_DW.L_DIA o10475639,
I do have problem with a table which have same structure of other table with indexes created being the same. Both tables have partions & subpartitions. Let us consider two tables table1 & table2. The problem i face is the index for table1 not being used when its being joined with other respective tables in a query. Whereas for table2 its working perfectly and cost is also less. I have used the explain plan and compared and query timing also takes more time. what might be causing it?
i have a union all query (two quiries i suppose) that runs fine and returns the results i am after.... when i try to use this query to create a view I get
SQL Command: CREATE OR REPLACE FORCE Failed: Warning: execution completed with warning
CREATE OR REPLACE FORCE VIEW "schema1"."name_of_view" ("column1", "column2", "column3") AS SELECT b.column1, a.column2, c.column3
Create Materialized View table_name Tablespace FG_Report Build Immediate Refresh Force Start with SYSDATE next SYSDATE+5/1440 As Select * from table_name@dblink;
after creating, i don't see indexes on the the materialized view created. where as in actual table we have indexes..
Can we replicate indexes along with Materialized views?
I have created 3 tables and some indexes, but these objects do not show up in dba_segments view. Is this a normal behaviour? Previously, with dictionary managed tablespace, I can specify the minimum extent to create, when the table/index is created. But I'm not sure how the locally managed tablespace work.
I'm using Oracle 11g R2 (11.2.0.1.0) for Microsoft Windows (x64), running on Windows 7.For the purpose of reproducing this issue, I have created the tablespaces as follow:
CREATE TABLESPACE CUST_DATA DATAFILE 'd:appasusoradataorcl11gr2CUST_DATA01.DBF' SIZE 512K AUTOEXTEND ON NEXT 256K MAXSIZE 2000K EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K SEGMENT SPACE MANAGEMENT AUTO;
I am writing a query and I did get it to work but it shouldn't be this hard, I feel like I am doing something wrong, and there has to be a more elegent solution.
This query works:
SELECT a.d FROM ( SELECT S_ID a, LOOKUP_DESC d, S_CODE f FROM SSS JOIN LOOKUP ON S_CODE LIKE LOOKUP_CODE UNION ALL
[Code]...
I feel like I should be able to execute the query like this: This query doesn't work:
SELECT a.d FROM ( SELECT S_ID a, LOOKUP_DESC d, S_CODE f FROM SSS JOIN LOOKUP ON S_CODE LIKE LOOKUP_CODE
I have a view, which has a union. (Union is required because of the nature of the data fetched). THis view is later joined with a global temp table which holds the -say employee Id the user selects.
So at runtime there is a join with the global temp table and the view. But the performance is really bad. I have tried using various hints, like materialize, /*+ CARDINALITY(gtmp 1) */ etc.
When i query the view alone,. the performance is good. When I remove the union, the performance is good. Some how with the union- there is a full table scan on one of the joining tables.
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 want drop some old partitions from big table but this will not increase free space on disk. So I want to move table with indexes to anothers tablespaces. What is the fastest way to do that? ALTER TABLE ... MOVE TABLESPACE ...? CTAS ? Or something else?
I have a table like MyTab(a int, b int), and I am required to create a primary key index and a non-unique index on this table using columns (a,b) in a specific table space.
The back end database is Oracle 10g.
Here's what I have tried so far, needless to say, unsuccessfully.
Alter Table MyTab Add Constraint c_1 primary key (a, b) Using Index (Create index mytab_idx on MyTab(a, b)) Using index tablespace results_index
So my question are:
1. is this is possible? if so, what is the correct syntax. 2. assuming it is possible, using this sort of construct before? it appears to be conflicting and inconsistent to me.
create index datastores_text on test_url_search ( doc ) indextype is ctxsys.context parameters ( 'Datastore TEST_URL' );
SELECT SCORE(1),DOC from TEST_URL_SEARCH WHERE CONTAINS(DOC, 'London', 1) > 0
This works. i like to more
e.g. multi words search e.g. ' London Games' where if London or games are present it return a URL with a score,
or
Search in the Chinese.
The only way I can see to do this is create different indexes as the attributes for 'URL_DATASTORE' are limited. If I can what does the 'Contains' statement look like?
I have one control table as below.I want to rebuild all indexes for the tables in control table.
The control table is having the following data.
SEQ_IDTABLENAME SCHEMA_NAME 1GEDIS_ORDER_FORM_STATES ALL 2GEDIS_NOTES ALL 3GEDIS_CARD_TYPE_AUDIT APRT 4FAX_HEADER OMS
In the control_table schema_name "ALL" means this is for 30 schemas(The table is existed in 30 schemas).Except for schema_name "ALL" ,the table is existed in the particular schema(The table is existed in the only one schema).
I tried the following code it is executing for all 30 schemas(ALL).But it is not executing for specific schemas.
CREATE OR REPLACE PROCEDURE Rebuilding_index IS l_sql VARCHAR2(4000); CURSOR cur_tab_schema IS SELECT tablename,schema_name FROM control_table3;
I have a materialized view "pro_mview",I am trying to refresh the MVIEW by using the following statement.
EXEC DBMS_MVIEW.REFRESH('pro_mview','C');
But I am getting the below error.
* Error at line 1: ORA-12008: error in materialized view refresh path ORA-00942: table or view does not exist ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431 ORA-06512: at line 1
I am able to fetch the data from that materialized view(pro_mview).
I have 3 tables as below. I am looking to get data from these 3 table at a time using a pl/sql block but unable to do. Is there any way that I can get the data in a single query.
PROMPT CREATE TABLE tst_fetch_vendor_data CREATE TABLE tst_fetch_vendor_data ( vendor_data_seq_no NUMBER NOT NULL, study_seq_no NUMBER NOT NULL, vendor_record_seq_no NUMBER NOT NULL, control_column_seq_no NUMBER NOT NULL, resolved_value VARCHAR2(4000) NULL, original_value VARCHAR2(4000) NULL, transaction_user VARCHAR2(30) NOT NULL, [code]....
Its just a temporary table, in which data comes and goes. I am using this in middle of a process.I am using it in a process like below--
--EXECUTE IMMEDIATE 'TRUNCATE TABLE TST_FETCH_VENDOR_DATA DROP STORAGE';
insert /*+ append */ into tst_fetch_vendor_data (select * from vendor_data vd where vd.control_column_seq_no in (select control_column_seq_no from temp_control_column)); dbms_stats.gather_table_stats('EPDSYSREP','TST_FETCH_VENDOR_DATA',ESTIMATE_PERCENT=>100, METHOD_OPT=>'for all indexed columns size auto',CASCADE=>True);
code to use that table..This table can contain data from 0 to 108000000 records.Now my questions are-
1. How much should I select sampling size (currently its 100%)Can I use dbms_stats.auto_sample_size, what will be the effect?
2. dbms_stats is good approach or should I use dynamic sampling.
3. what about the approach using CTAS instead of inserting data through insert.
4. What about pl/sql table with index or with clause query.
5. Do I need to rebuild index after inserting data into table.
I have two tables with almost the same columns. Table A has 50 columns, Table B has 51 columns, which are defined exactly the same except one more column as LOAD_TIME.
Is there any easy way for me to move the data from Table A to B? the Extra column LOAD_TIME will be set as current system time. All I want is to avoid list all 50 column names. The following SQL has error ORA-00923 error.
am trying to add a validation in Oracle forms using Oracle DB.in a Oracle table consider there is a record named 'Netbook' then if I try to create another record with name 'Netbooks', then a Oracle warning message should be displayed stating similar name 'Netbook' is already available. same way, if NETBOOKS is already available in table and if user try to create another record NETBOOK then same warning message netbooks is already available should be shown.
- Imagine I have an table with several ZIPCODE. - Imagine I want to return from that table the existence of several zipcodes I need to get. - Imagine that I need to return both zipcodes that exists and both zipcodes that not exist.
The solution I've found to do this, was using the Pivot method, like the sample below, if there is another way to return anything like that. Return the zipcodes that exist, and the zipcodes that does not exist also!
Insert into GSCIS.AAA_ADDRESSZIPCODES (ZIPCODE,NAME,COD_PLACE) values (1100,'Portugal',2); Insert into GSCIS.AAA_ADDRESSZIPCODES (ZIPCODE,NAME,COD_PLACE) values (1150,'Portugal',2); Insert into GSCIS.AAA_ADDRESSZIPCODES (ZIPCODE,NAME,COD_PLACE) values (1000,'Portugal',1); Insert into GSCIS.AAA_ADDRESSZIPCODES (ZIPCODE,NAME,COD_PLACE) values (1200,'Spain',2);
Select using Pivot:
SELECT * FROM (SELECT distinct zipcode FROM aaa_addresszipcodes group by zipcode ) PIVOT ( count(zipcode) FOR zipcode IN (1000,1100,1200, 1150, 2000) )
I am using: Oracle SQL Developer (3.0.04) Build MAin-04.34 Oracle Database 11g Enterprise Edition 11.2.0.1.0 - 64bit Production Sample dataTable
with t as ( select to_date('8-18-2013','mm-dd-yyyy') dt, '123_' ticket_origin, '123' ticket_destination,101 startid, 101 origin, 0 destination, 'origin' objecttype, 85 amount, 100 area from dual union all select to_date('8-18-2013','mm-dd-yyyy'), '123', '123_',101, 0, 103, 'destination', 85, 100 from dual union all select to_date('8-18-2013','mm-dd-yyyy'), '123', '123_',0, 0, 103, 'destination', 85, 100 from dual union all select to_date('8-17-2013','mm-dd-yyyy'), '124._', '124.', 105, 105, 0, 'origin', 150, 200 from dual union all select to_date('8-17-2013','mm-dd-yyyy'), '124._', '124.', 106, 105, 0, 'origin', 150, 200 from dual union all [code]..........
Is there a way to check in that date grouping for matching ticket_origin and ticket_destination when there may be two or more rows difference between them that does not allow me to use Lead or Lag function. Is it also possible do so without using the amount column? I also would like to identify if they are in the same area when paired (this I believe works after getting table sorted like so below then use lead lag after having the order by done) I am trying to get something like this table with results as
select to_date('8-18-2013','mm-dd-yyyy') dt, '123_' ticket_origin, '123' ticket_destination,101 startid, 101 origin, 0 destination, 'origin' objecttype, 85 amount, 100 area from dual union all select to_date('8-18-2013','mm-dd-yyyy'), '123', '123_',0, 0, 103, 'destination', 85, 100 from dual union all select to_date('8-17-2013','mm-dd-yyyy'), '124._', '124.', 105, 105, 0, 'origin', 150, 200 from dual union all select to_date('8-17-2013','mm-dd-yyyy'), '124.', '124._', 105, 0, 106, 'destination', 150, 300 from dual union all select to_date('8-17-2013','mm-dd-yyyy'), '127_', '127', 108, 108, 0, 'origin', 50, 600 from dual union all [code]...........
