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

Create Reverse Key Index On Table

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

SQL & PL/SQL :: Create A Table With Constraint And Index

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

SQL & PL/SQL :: Unable To Create Index On Large Table

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

SQL & PL/SQL :: Create Table As Select On 10.2.0.5?

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

Create Table From Select Query?

Sep 13, 2004

Can we create a table from a Select query ?

View 5 Replies View Related

Create Table Not Replicating SQL Select Results?

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

SQL & PL/SQL :: Create Non-cluster Index On A Clustered Index?

Jul 11, 2012

Can we create non-cluster index on a clustered index?

View 5 Replies View Related

PL/SQL :: Index Range Scan And Table Access By Index Rowid Versus Table Access Full

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

Performance Tuning :: Local Index Versus Global Index On Partitioned Table

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

Performance Tuning :: Force Index If Table Not Using Index?

Aug 9, 2013

How to force an index if the table not using the index?

View 10 Replies View Related

Last Date / Time When Index Used For Select

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

Multi Column Index Select Performance

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

Check Index Used In Oracle Select Query?

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

PL/SQL :: Create Bitmap Index?

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

Select Statement Gives Different Results Using Bitmap Or Normal Index

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

SQL & PL/SQL :: Select Query Received In Unsorted Way Even Though Index Defined

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

PL/SQL :: How To Display Pseudo Static Index Using Select Statement

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

How To Create Index On Materialized View

Aug 6, 2010

How to create index on materialized view which is refreshed daily basis.

View 3 Replies View Related

Performance Tuning :: How To Create Index

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

SQL & PL/SQL :: Create Primary Key Without Creating Index?

Jul 12, 2012

how to create a primary key with out creating an index?

View 10 Replies View Related

SQL & PL/SQL :: Create Index On Multiple Tables?

May 21, 2013

we create index on multiple tables in oracle

View 12 Replies View Related

SQL & PL/SQL :: To Create A Bitmap Index On Partition Key

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

How To Create Unique Index On A View

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

Domain Index - Select Query With Clause Does Not Return Any Records

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

Performance Tuning :: Nested Select / Instead Of Trigger And Views - No Index Used?

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

Enterprise Manager :: What Is The Difference Between Create External Table Vs Create Table

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

Can Create Unique Index On Subset Of Record

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

Any Rule In Documentation When Create Partition Index?

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

SQL & PL/SQL :: How To Create Dynamically ALTER INDEX Command

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







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