SQL & PL/SQL :: Create Table As Select With Index
Nov 10, 2010
Is it a possible to create table using clause below together with index ?
create table the_table
as
select col1, col2 from table2
I got procedure which create a table in the schema B. The procedure is called from schema A. But when I write into procedure query for create index
then I got a error:
ORA-01031: insufficient privileges when
...executing
Therefore I think about to create table together with index.
begin
B.proc.cre_table;
end;
View 6 Replies
ADVERTISEMENT
Feb 25, 2013
difference between the ways we create Reverse Key Index on the table.The two ways are as below:
1) CREATE INDEX <Index_Name> ON <Table_Name>(<Column_Name) REVERSE ;
2) CREATE INDEX <Index_Name> ON<Table_Name>(REVERSE(<Column_Name>)) ;
Which one is the appropraite one.
Are both going to act in the same way.
I created index in both ways one by one on the table.But when I fire the select statement against the same table, The explain plan doesn't show any cost and it shows the full table scan in both ways.The select statement used is as below...I want to compare the column with string RBO(i.e('RBO%') at the start.
SELECT *
FROM <Table_Name>
WHERE REVERSE (<Column_Name>) LIKE '%OBR' ;
The select statement is giving me the correct result. Only the explain plan is showing Full Table Scan.can we use this reverse with IN operator also in the same way by reversing the values.
View 3 Replies
View Related
Apr 7, 2010
I use Oracle 10.0.2.0.1.If I create a table with constraint key; after that I create an unique index key, I got an error. Does it mean when I create a table with constraint the unique index are automatically created and I could not create index key as I did as below?
create table test_const(ename varchar2(50) not null,
key_num number not null,
descr varchar2(100),
constraint constraint_test_const unique (ename, key_num));
create unique index test_const_idx on test_const
(
"ENAME","KEY_NUM"
)
tablespace tmp_data;
Error report: SQL Error: ORA-01408: such column list already indexed 01408. 00000 - "such column list already indexed" added [pre] tags by Sriram
View 3 Replies
View Related
Sep 30, 2012
I am trying to create a new index on large table of size around 100GB. but i am getting the following error:
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP.
temp tablespace size is : 20 GB.
does it mean that the whole index will be created at temp tablspace first?
View 3 Replies
View Related
Oct 26, 2011
running on 10.2.0.5 and above satisfies the following:
having locally managed tablespace with assm enabled
create table t1 (n1 number) storage (next 1M);
insert into t1 values (1); commit;
create table t2 storage (next 2M) as select * from t1;
This will indeed set the storage option next to 2M;
The same situation in 10.2.0.4 will allways set next option for the ctas statement to the initial value of table t1.
Is this an impact of patch set 4 (10.2.0.5)? Why does it differ from 10.2.0.4 to newer releases?
View 3 Replies
View Related
Sep 13, 2004
Can we create a table from a Select query ?
View 5 Replies
View Related
May 10, 2012
I have a SQL statement that returns a set of columns...but...when I create table as <SQL statement> I get the same columns but with 2 of the columns containing each others data, e.g:
SQL Select:
COL1 COL2 COL3 COL4
___________________________________
AND10200000017805CG-4CG-3
Create Table as <SQL Select>:
COL1 COL2 COL3 COL4
___________________________________
AND10200000017805CG-3CG-4
The SQL Select is correct and the Create Table As <SQL Select> is wrong.
Here is my SQL:
Create table ALTERNATENUMBERS as
SELECT ctry,
id,
MAX(DECODE(tp,'EN', RN)) EN,
MAX(DECODE(tp,'RN', RN)) RN,
MAX(DECODE(tp,'AN', RN)) AN
[code]....
Unfortunately I cannot give you any data (too much of it) and small scale testing works, it's only when I run it on the 11million records do I get some (not all), just some of the data being mixed up between columns.
Now, I've tried:
1. Using SQLPLus - no joy
2. Creating the Table and then inserting the data into a blank table - also no joy
3. Using a VIEW - no joy, listagg doesn't work in VIEW tables
I do understand that without data it's hard to replicate the issue but why this statement works as a SELECT but when written to a table has data anomolies?
View 4 Replies
View Related
Jul 11, 2012
Can we create non-cluster index on a clustered index?
View 5 Replies
View Related
Oct 5, 2013
Let's consider such table that all rows fit into single block:
SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530;
Table created.
SQL> create index i_test on test(id);
Index created.
SQL>
SQL> begin
[code].....
why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2
View 8 Replies
View Related
Jun 28, 2011
I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.
View 6 Replies
View Related
Aug 9, 2013
How to force an index if the table not using the index?
View 10 Replies
View Related
Jul 26, 2011
Is there a way I can find what the last date/time and index was used for a select...
I have a table with several indexes on them, which I beleive are not being accessed.
I use the following the query to find indexes that where not accessed in a while but this I believe is limited my my workload repository retention, which is set to 90 days.
select index_name from dba_indexes where table_name='<table name>'
and index_name not in (select c1 from(
select p.object_name c1, p.operation c2, p.options c3, count(1) c4
from dba_hist_sql_plan p, dba_hist_sqlstat s
where p.object_owner = 'MTAS' and p.operation like '%INDEX%' and p.sql_id = s.sql_id
group by p.object_name, p.operation, p.options
order by 1,2,3))
Without increasing my repository retention is there a way I can get the last date/time, which an index was used instead of just saying it has not been used in 90 days (retention setting). Is this information kept in the SQL plan?
View 2 Replies
View Related
Dec 11, 2012
explain slow performance of multicolumn indexes on oracle 11g R2 observed in the following scenario? A multi-column index (b-tree index) not partitioned, not unique, not reversed with 3 columns.
A series of queries are run using all 3 columns. The performance hit comes when the first order column values changes. So, maybe after 10 select queries the value changes. The 2nd and 3rd order columns are changing throughout the series of select calls, but no performance bottleneck it hit then.
View 2 Replies
View Related
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
Jan 27, 2013
i want to create a bitmap index,but getting the error shown below..i created primary key on column dname of dept and unique constraint on empno of emp.
SQL> create bitmap index bindx on dept d(d.dname) from emp e,dept d where e.deptno=d.deptno;
create bitmap index bindx on dept d(d.dname) from emp e,dept d where e.deptno=d.deptno
*
ERROR at line 1:
ORA-25954: missing primary key or unique constraint on dimension
View 1 Replies
View Related
Jan 10, 2012
we have a strange symptom in a database Oracle 11.2.0.2 EE. Following question comes from our application developers.
The following SQL statement:
SELECT
v.reporting_month,
sum(v.f_s) "REV_S",
sum(v.f_f) "REV_F",
sum(v.f_c) "REV_C",
[code].......
gives different result when we exchange the index ksr_valid_until_i on table kreditkarten_sets_rs. For some reasons we changed the index from bitmap to normal and are getting different results. Switching back gives us the same results as before. When we avoid the usage of this index in the statement than we are getting the same results as when we are using the normal index.
View 4 Replies
View Related
Jul 5, 2010
I have a table with an index on the file_id column, defined in the same way in a test site and a production site.The only difference of these two tables is the amoun of data contained on it.the test site table contains about a million of records.the production table contains about 17 millions of records.
When I do a select on the table in test site, it returns the information in a sorted way.however, when I do the same select on the production site, the information is received in an unsorted way.
I assume that the index in production site might need to be rebuilt, because usually a big amount of data is extracted and deleted from the table and new information is inserted constantly.this situation does not occur in test site, information change is not very usual.
if rebuilt of index would actually to get the results on a sorted way, or the only way to get it is to add an "order by" statement in the query statement.
View 9 Replies
View Related
Jul 20, 2012
I have to display a 24 hours time as an index. (00:00 - 23:00) That is easy, if the data exists.
But, if the data is not regularly exists on specific hour (the time/hours is not regular, i.e: 00:00, 01:00, 05:00, 08:00, 10:00, 11:00, 23:00), then it is OK for a TABLE, but not for a CHART.
Here is the story:
I wrote an SQL statement that can "choose" what to display respectively to the the available time. The result will be displayed on the chart: Days or Time is used for X-Axis, and the respective Value on the Y-Axis.
Example:
+ if the data consists of "days" (i.e: the last 5 days from now, 16/07/2012, 17/07/2012, 18/07/2012, 19/07/2012, 20/07/2012), then the chart will show those dates on the X-Axis.
+ if the data consists of "hours" (i.e: 1 single day, from 00:00 hour - 23:00), then the chart shows hours on the X-Axis.
If the 'hours' are not regular, means: there is no data on specific hours, the query result is also not regular, means: the X-Axis-value 'jumps' irregularly.
Question:
Is it possible to query our own static value?
for example:
select 'hello' from dual --> result: "hello", 1 column, 1 row
But how to display this in multiple rows, i.e.:
Time
*****
0
1
2
3
4
5
...
...
23
Note: the X-Axis on the chart template can be only customized for 1 single mode, example: "Days", but of course it is not going to show up properly if the query result are in "Hours". It means: I have to find the workaround on the SQL Query.
DB: ORA 11
View 9 Replies
View Related
Aug 6, 2010
How to create index on materialized view which is refreshed daily basis.
View 3 Replies
View Related
Mar 30, 2013
I am going through this scenario:
* 35 | ID TABLE ACCESS BY INDEX ROW | S_ORG_EXT | 3064K| 2472M| | 1 (0)| 00:00:01 |
| 36 | INDEX FULL SCAN | S_ORG_EXT_U1 | 14 | | | 1 (0)| 00:00:01 |
Predicate Information (identified by operation id):
---------------------------------------------------
35 - filter("T2"."ACCNT_FLG"<>'N' AND ("T2"."INT_ORG_FLG"<>'Y' OR "T2"."PRTNR_FLG"<>'N'))
This unselective index scan on step 36 of the explain is returning 14 rows but optimizer is selecting 3064 K rows from the table .
I tried creating combined index on all 3 columns mentioned in the predicates for 35th step , but that is not utilized .
how to index this whole expression ::--
(ACCNT_FLG<>'N' AND (INT_ORG_FLG<>'Y' OR PRTNR_FLG<>'N'))
Something like CREATE INDEX XYZ on table((ACCNT_FLG<>'N' AND (INT_ORG_FLG<>'Y' OR PRTNR_FLG<>'N')) compute statistics ;
View 3 Replies
View Related
Jul 12, 2012
how to create a primary key with out creating an index?
View 10 Replies
View Related
May 21, 2013
we create index on multiple tables in oracle
View 12 Replies
View Related
Dec 3, 2012
I have an IOT table with partitioned on list. I have p1,p2 and p3 partitions. Now I would like to create a bitmap index on partition key.
I gave ALTER TABLE .. MOVE MAPPING TABLE;
But getting the below error,
ORA-28660: Partitioned Index-Organized table may not be Moved as a whole.
View 1 Replies
View Related
Aug 24, 2012
11.2.0.1...How do I create an index on a view or any workaround that my view won't get duplicates?
SQL> create unique index indx01 on db_backup_details_vw(id);
create unique index indx01 on db_backup_details_vw(id)
*
ERROR at line 1:
ORA-01702: a view is not appropriate here
View 7 Replies
View Related
Jun 30, 2012
I have created domain indexes on text columns of a materialised view to use "contains" clause when searching for data. The select query with "contains" clause does not return any records, however I was able to retrive data using via regular query using a like search.
-> will exec ctx_ddl.sync_index('index_name')'resolve my problem?
-> since the view is a materialized view, how can i make sure that the latest data added are also picked up?
View 2 Replies
View Related
Sep 8, 2009
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
5 rows selected.
I have a problem with views and nested selects which I cannot explain. Here is a trimed down version of the research I have done. notice the following:
1) all code is executed from the same user CDRNORMALCODE. this user has all views and procedural code
2) all data is owned by a different user CDRDATA. This user has no views and no code.
My problem is this:
If I reference the table directly with a delete statement that uses a nested select (i.e. IN clause with select), the index I expect and want is used.But if I execute the same delete but reference even the most simple of views (select * from <table>) instead of the table itself, then a full table scan is done of the table.
Here is an execute against the table directly (owned by cdrdata). Notice the reference to the table in the table schema on line 3. Also please notice INDEX RANGE SCAN BSNSS_CLSS_CASE_RULE_FK1 at the bottom of the plan.
SQL> show user
USER is "CDRNORMALCODE"
SQL>
SQL> explain plan for
2 delete
[code]...
OK, here is an update. The views I am useing normally have instead of triggers on them. If I remove the instead of trigger the problem looks like it goes away, when I put the trigger back the problem comes back.But why would an instead-of-trigger change the query plan for a view?
SQL> DELETE FROM PLAN_TABLE;
5 rows deleted.
SQL> explain plan for
2 delete
3 from BSNSS_CLSS_MNR_CASE_RULE_SV
[code]...
View 10 Replies
View Related
Apr 29, 2011
What is the difference between CREATE EXTERNAL TABLE Vs CREATE TABLE .?
Is CREATE EXTERNAL TABLE included in CREATE TABLE?
View 3 Replies
View Related
Apr 9, 2013
I have following records in table (T)
Idtype_cdNbrDt
1A1001/1/2013
2A101Null
3B100Null
4C100Null
5A100Null
Based on the type-cd, I need to create Unique index on Nbr & Dt columns
In above record set, I want to create Unique index on type_cd=A and not on B&C (Here i expect duplictes for a combination of Nbr & Dt)
Can we create Unique index on subset of record?
View 1 Replies
View Related
Feb 20, 2012
Is there any rule in documentation, when create partition index? For tables, it is said to partition when table is greater than 2GB, but what about index? WHat size it should have to partition?
View 1 Replies
View Related
Nov 15, 2010
how I can create an index dynamically? The DBA wants me to put this code below in the beginning of a PL/SQL package. At first, I tried by just putting the below syntax in the code, but I get an 'Alter' is not a valid identifier.
alter index ZZAP_selected_invoices_n1 rebuild online;
And then once the job completes...
alter index ZZAP_selected_invoices_n1 unusable;
View 8 Replies
View Related