SQL & PL/SQL :: Querying A Column In Oracle 10g
Jan 15, 2013
I'm having truble by querying a column in Oracle 10g. This is my situation I wrote a standard Oracle Query to show only the domain name of user's registration. The column's alias is called MAILS so far so good, the main problem is when I tried to shortener the result to show only Gmail, Hotmail and Yahoo.
Also I had the same issue when I tried to use GROUP BY the field "MAILS", but when I use the order by clause with that field it run perfectly. I think a test case is not necesarry because there are common mails address.
SELECT
SUBSTR(SUBSTR(mail,INSTR(mail,'@',1)+1,50),1,
INSTR(SUBSTR(mail,INSTR(mail,'@',1)+2,50),'.',1)) "MAILS",
COUNT(SUBSTR(SUBSTR(mail,INSTR(mail,'@',1)+1,50),1,
INSTR(SUBSTR(mail,INSTR(mail,'@',1)+2,50),'.',1))) "TOTAL"
FROM USER_REGISTRATION
WHERE TRUNC(FECHAALTA) BETWEEN TRUNC(TO_DATE('01/01/2012','DD/MM/YYYY')) AND TRUNC(TO_DATE('31/12/2012','DD/MM/YYYY'))
GROUP BY SUBSTR(SUBSTR(mail,INSTR(mail,'@',1)+1,50),1,
INSTR(SUBSTR(mail,INSTR(mail,'@',1)+2,50),'.',1))
ORDER BY MAILS DESC;
View 3 Replies
ADVERTISEMENT
Mar 17, 2011
I have a CLOB column called XML_DATA that has (not-surprisingly) xml data in it that's housed inside a table called HMS_XML_TRANSFER. It has been giving me a headache because I'm unable at this point to use the xml field as a condition to get its TRANS_SEQUENCE number. The where clause doesn't work.
SELECT TRANS_SEQUENCE, XML_DATA
FROM HMS_XML_TRANSFER
WHERE EXTRACTVALUE(XMLTYPE (XML_DATA), '/INTERFACES/INTERFACE/BODY/IFI0057[ACTIVITY_CODE = "2201-020742"]');
The only test that I have been able to get working is the one below.
SELECT TRANS_SEQUENCE, EXTRACTVALUE(XMLTYPE (XML_DATA), '/INTERFACES/INTERFACE/BODY/IFI0057/ACTIVITY_CODE')
FROM HMS_XML_TRANSFER
WHERE TRANS_SEQUENCE = '8191602';
It will give me the ACTIVITY_CODE element so I know I can pull data from the XML but I can't do the reverse in the first example which is what I need because I don't know the TRANS_SEQUENCE number, I just know the ACTIVITY_CODE.
I have over 202 error messages logged in Teradata SQL from my many and varied attempts to get this to work using every example I could find online.
As an example this has not worked either...
WHERE EXISTNODE(XML_DATA, '/INTERFACES/INTERFACE/BODY/IFI0057[ACTIVITY_CODE = "2201-020742"]') = 1;
So the question... How do I properly form my SQL statement so I can use the XML column's ACTIVITY_CODE element to get the TRANS_SEQUENCE column field? Oh and I'd like to see both columns in the result.
Below is the version of Oracle I'm using, the description of the Table HMS_XML_TRANSFER, and a sample of the XML that comes from XML_DATA. I can't seems to get tabs working.
===============
ORACLE VERSION
===============
SQL*Plus: Release 9.0.1.3.0 - Production on Thu Mar 17 08:18:15 2011
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
=========================
TABLE HMS_XML_TRANSFER
=========================
Name Null? Type
--------------- --------- --------------
TRANS_TYPE NOT NULL VARCHAR2(10)
DATE_IN NOT NULL DATE
DATE_PROCESSED DATE
STATUS VARCHAR2(8)
[code]....
View 8 Replies
View Related
Apr 16, 2012
I have a table in Oracle with a column userid and i have a userid column in Active Directory. based on this i want to query the Network ID and update in one of the Group in the Active Directory. how to get the results?
View 2 Replies
View Related
Aug 14, 2012
We are using oracle database 11g R2 on REL 5 and i have an SQL server database used by one of our application. In my Oracle database, i want to query one table in my SQL server database and to link it in a table in Oracle database to compare data.
Is there a way like database link or something else to do this need. Send a link or pots here step by step the way on how to accomplish my query.
View 2 Replies
View Related
Mar 26, 2013
I have installed unixodbc 2.3.1, postgres odbc driver (psqlodbc-07.03) and dg4odbc 11.2..On querying : select sysdate from dual@dblink_postgresql, the following error occurs:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[unixODBC][Driver Manager]Can't open lib '/oracle/psqlodbc/lib/libpsqlodbc.a' : file not found {01000}
ORA-02063: preceding 2 lines from DBLINK_POSTGRES
Similar error appears in the trace file as well.
[unixODBC][Driver Manager]Can't open lib '/oracle/psqlodbc/lib/libpsqlodbc.a'
$ isql -v postgresql postgres postgres@2012
[01000][unixODBC][Driver Manager]Can't open lib '/oracle/psqlodbc/lib/libpsqlodbc.a' : file not found
[ISQL]ERROR: Could not SQLConnect
However the file is present in the location and has no permission related problems.
$ pwd
/oracle/psqlodbc/lib
$ ls -lrt
total 2952
-rwxr-xr-x 1 oracle oinstall 663 Mar 25 15:28 psqlodbc.la
-rw-r--r-- 1 oracle oinstall 731419 Mar 25 15:28 libpsqlodbc.a
-rw-r--r-- 1 oracle oinstall 12215 Mar 25 22:11 win_md5.o
-rw-r--r-- 1 oracle oinstall 18100 Mar 25 22:11 options.o
[code]....
View 11 Replies
View Related
May 20, 2010
I have the following 3 columns in my VERSIONS table.
MAJOR
MINOR
MICRO
The MAJOR,MINOR and MICRO columns create a unique composite primary key. I need to query out the last version by doing the following 3 queries and combine them.
select max(major) as max_major from versions
select max(minor) as max_minor from versions where major = :max_major
select max(micro) as max_micro from versions where minor = max_minor and major = max_major
Is there any way I can query out max(major),max(minor),max(micro) in a single query.
View 7 Replies
View Related
Jul 5, 2012
I am trying to subtract the time from where status = Delivered from the status = Picked Up. All the data is in the same table. Let's call it table1 and I want to return the values: PTN, NAME and the time difference. Is this possible to do?
PTN NAME DATE STATUS
11014419 Joe Dickson 2012-06-25 14:55:58 Delivered
11014419 Mike Draia 2012-06-25 14:28:17 Loaded
11014419 Bob Geber 2012-06-25 13:14:31 Received
11014419 Bob Geber 2012-06-25 13:14:31 Picked Up
11014419 Bob Geber 2012-06-25 13:14:31 Printed
View 3 Replies
View Related
Feb 2, 2011
I have a temporary table (with on commit preserve rows property) which is populated thru insert into command from a procedure. After which, i need to query the records from the populated temp table.
However, my query returns nothing. My procedure works fine cause i tried executing it to populate a regular table and it is ok. However, it shows no output in the temp table cause probably it is creating another session. How do i select the rows from the temp table after populating it from a procedure.
View 3 Replies
View Related
Aug 23, 2007
SELECT * FROM table WHERE id = $x
$x being a range of non-consecutive values like so:
1,3,5-9,13,18,21 and so on...
I realize I can query using an array of operands and such, but these ranges will be in upwards of 100 or more items. I want to minimize the number of queries I have to do and the length of them. Is there any resource you can point me to that can optimize something like this?
View 3 Replies
View Related
Apr 21, 2012
I have flattened customer dimension table and I would like to query it with other dimension table like address. I write a query, where I join address table twice to get permanent, secondary, and work addresses, but customer and address tables are huge that causing performance issue. Is any other ways to join flatten table with address dimensions than join it twice.
CREATE TABLE CUSTOMER
(
cust_sk NUMBER NOT NULL ,
cust_src_id VARCHAR2(20) NOT NULL ,
rec_eff_dt DATE NOT NULL ,
last_name VARCHAR2(75) NULL ,
first_name VARCHAR2(30) NULL
brth_dt DATE NULL ,
[code]......
View 6 Replies
View Related
Apr 16, 2013
I successfully created the following DBLink in my DEV environment:
CREATE PUBLIC DATABASE LINK PROD
CONNECT TO USER1 IDENTIFIED BY pwd98
USING 'PRD';
I have a table in both schemas. In PROD it has 0 recs, in DEV it has 134 recs.
select count(*) from hold@PROD.
The above query gives me 134 recs instead of 0!
Here is my TNSNAMES entry for PRD:
PRD=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=localhost)
(PORT=1529)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=PRD_USR)
))
View 14 Replies
View Related
Apr 24, 2012
I am inserting data into a global temporary table and then using 'parallel' hint to query from this temporary table. I remember reading that the queries on the temp table may not run in parallel as the parallel sessions may not be able to see the data in the temporary table
However the execution plan as well as px_session, v$sql indicate that the query on the temporary table in fact run in parallel mode
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID 7d68g52g0mskz, child number 0
-------------------------------------
select /*+ gather_plan_statistics parallel(t,4) */ * from dbo_gtt t order by id,object_id
Plan hash value: 5815349
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------
| 1 | PX COORDINATOR | | 1 | | 99999 |00:00:01.46 | 3 | | | |
|
| 2 | PX SEND QC (ORDER) | :TQ10001 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|
| 3 | SORT ORDER BY | | 0 | 1 | 0 |00:00:00.01 | 0 | 11M| 1311K| 424K (0)|
|
| 4 | PX RECEIVE | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|
| 5 | PX SEND RANGE | :TQ10000 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|
| 6 | PX BLOCK ITERATOR | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|
|* 7 | TABLE ACCESS FULL| DBO_GTT | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access(:Z>=:Z AND :Z<=:Z)
select px_servers_executions from v$sql where sql_text like 'select%dbo_gtt t%';
PX_SERVERS_EXECUTIONS
---------------------
0
4
select sid, qcsid, server#, degree from v$px_session where qcsid = 228;
SID QCSID SERVER# DEGREE
---------- ---------- ---------- ----------
247 228 1 4
196 228 2 4
224 228 3 4
234 228 4 4
226 228 1 4
252 228 2 4
212 228 3 4
229 228 4 4
228 228
9 rows selected.
unfortunately I do not have access to get trace (tkprof) report. What must have happened during the execution?
View 3 Replies
View Related
Nov 14, 2011
I am using SQL Developer.I am self-teaching myself PL/SQL. What I am trying accomplish is to run a select query across many database links at runtime and to insert that query onto a local table. So far, I have only gotten as far as querying the database link names. I am stuck at where my variable calls the database link name. It does not recognize the database link name, and I can't quite grasp the reason why. Below is the first part of my script which does retrieves the column values no problem:
001 SET SERVEROUTPUT ON
002 DECLARE
003 db_link_varVARCHAR2(30);
004 source_cursorINTEGER;
005 destination_cursorINTEGER;
006 src_csrINTEGER;
007 dst_csrINTEGER;
008 rundate_varDATE;
009 instance_name_var VARCHAR2(30);
[code]....
If I break the script down to the bare select query, it will query absolutely fine.If I run the script in its entirety, it will not pick up the variable dbl as a dynamic database link. 02019. 00000 - "connection description for remote database not found"
If I comment out line 031 and prevent the looping of querying of database links (which will only fetch the first value of db_link from dba_db_links), the script will complete and I will have a row inserted into my local table.
I am suspecting it's the looping that is incorrect but I understand it is not going to do anything beyond line 059.
View 8 Replies
View Related
Oct 9, 2013
I have table with 129 million records.
If I just to select count(*) on the table its taking more than a minute in Sql Developer.
The table structure is as below, Primary key is a sequence and then 3 foriegn keys and one non-unique index on the date column.
<Table_Name>
column1 NOT NULL NUMBER ( Primary Key)
column2 NOT NULL NUMBER ( FK1)
[Code].....
View 1 Replies
View Related
Oct 14, 2013
Our company database is Oracle based and we use SQL Developer to pull out needed data.Using a snippet borrowed from a co-worker, I have put together a query that, among other things, pulls a list value out of an xml clob field and displays it in the query results. My query as it stands right now is below, followed by an example snippet of the xml clob that I am pulling from. The reason for the "query within a query" is because the base query could return multiple entries and I only want the one with the most recent date.
select * from
(Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date,
to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag
from ws_transactions
Where Wtr_Service_Tag In ('20458749610')
And Wtr_Req_Xml Like ('%CSM%')
Order By Wtr_Receive_Date Desc)
where rownum = 1;
[code]....
This query is only able to pull the first value in the list.How can I edit this query to pull all of the list items when there are more than 1 (preferably concatenated into one column in the query results)? I have another field, in a separate table, that I can pull from to get the number of list items.
This one may be more complex. As currently written, the query pulls a fixed number of characters from the xml clob and either returns not enough data, or too much because the values I need to pull could be of varying lengths. I have no way to query what those lengths might be.
View 28 Replies
View Related
Feb 10, 2012
We have a table with huge data which is skewed on a 'status' column. The 'status' column has 6 distinct values with 1 particular value occupying 80-85% records.
In the batch process we query the data on the status and process the retrieved records. My senior is insisting on partitioning which I see not much feasible considering cost implications just for a part of functionality
See there are 6 status 'A','B','C','D','E','F'
with 'A' occupying 80% records
'B' to 'F' occupies 2% till 14% records in the table(approx)
1) Create a conditional index on status (using case) to have records with all statuses except 'A' Then create If-ELSE structure
IF input parameter is 'A'
select /*+ FULL Parallel(t) */ * from t where status='A';
ELSE
Select /*+ INDEX (t conditional_index) */ * from t where status in ('B','C');
END IF;
I want to create conditional index here for 2 reasons
1] since it will have values for status except 'A' this nullify the chance that this index will be picked up when status='A' will be queried
Thus making the performance worst (status ='A' is for 80% records) - The IF-ELSE is additional protection
2] Less impact on the DMLS as the index will not be on status='A' which contribute to large chunk of records
2)Populate a dummy table which would contain rowid and status. Since the business closes at 21:00 and batch process starts at 21:30
Between these times periods refresh the dummy table every day using merge (to catch business transactions during the day)
Now during the batch process retrieve records from the main table using the rowids in the dummy table depending on the input status value
3)Create index on status
Make sure hard coded status values are used in the database procedures
Gather stats with the histograms
And leave it to the Optimizer to choose the best possible path
View 3 Replies
View Related
Nov 11, 2012
The Item data for individual cycles is as below.
Item_tbl
ItemRundate StddateStatus
P103-Nov-1203-Nov-12A
P104-Nov-1204-Nov-12D
P2 04-Nov-1203-Nov-12A
The requirement is I have to get the details of all data of previous Active cycle(status A) when the Item became disabled(status = D) for Input date.
In above case,since for Item P1 and on cycle date 04-Nov-12,status is D,I have to consider the previous active cycle which is 03-Nov-12. Based on above std date,the data is queried from another table to get all the Items. Item P2 should not be considered in above case.
Below is the code which I have written which considers the rundate as Input parameter.
-- To get the Items disabled for Input date
with Itemdisabled as
(
select item,stddate maxcycledate
from Item_tbl
where rundate = stddate
[code]....
In above case,I'm querying the Item_tbl twice once for getting the disabled Items and once for getting the Previous cycle which is active.
Is there any way to query above only once and get the required results using Lag/Lead functions etc.
View 5 Replies
View Related
Nov 27, 2012
I am trying to fetch the data from system table "FLOWS_020100.WWV_FLOW_ACTIVITY_LOG1$" in Oracle Express edition 10.2.0.3 database, using the OCI library on Windows with C++
This table has a TIMESTAMP column of date type.
I've the following query as below:
select TIME_STAMP from FLOWS_020100.WWV_FLOW_ACTIVITY_LOG1$ where TIME_STAMP > to_date('31/OCT/12 23:59:59', 'DD-MON-YY HH24:MI:SS') order by TIME_STAMP asc;
For this, first I am preparing the query as below:
select TIME_STAMP from FLOWS_020100.WWV_FLOW_ACTIVITY_LOG1$ where TIME_STAMP > :PKVAL order by TIME_STAMP asc;
Before calling OCIExecute(), I am setting the PKVAL buffer to following value:
"to_date('31/OCT/12 23:59:59', 'DD-MON-YY HH24:MI:SS') "
& calling the OCIBindByName function with data type as SQLT_DATE.
But when the OCIExecute is called, my program crashes with access violation
what is the correct way to pass the date value in query to OCI? How do we bind the date values?
View 1 Replies
View Related
Sep 25, 2012
query to get the name and size of all the databases associated to grid..
I need to find this to do capacity planning in our environment.
View 6 Replies
View Related
Jul 22, 2009
I'm trying to do a pivot query in oracle to get the years from a column and make a separate column for each. I found an example of the code to use on the internet and i changed it for my own tables but i'm getting errors. Namely a "FROM keyword not where expected" error at the beginning of the 'avg(...' statements.
I have copied the code used in
select stud_id, 2006, 2007, 2008, 2009
from (
select stud_id,
avg(case when year=2006 then ((present/poss)*100) else null end) 2006,
avg(case when year=2007 then ((present/poss)*100) else null end) 2007,
avg(case when year=2008 then ((present/poss)*100) else null end) 2008,
avg(case when year=2009 then ((present/poss)*100) else null end) 2009
from attendance.vw_all_attendance_perc
group by stud_id
);
View 11 Replies
View Related
Nov 28, 2011
When we backup .arc files we issue a RMAN command similiar to this:
KEEP_ARCHIVE_HOURS=2
rman <<EOT
connect target ....
connect catalog ...
sql "alter system archive log current";
[code]....
The problem is at times there may not be any archive files that meet this criteria and RMAN throws an error
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20242: specification does not match any archived log in the recovery catalog
I was considering using the following code to see if there are any available .arc files that meet the backup criteria.
set pagesize 0 feedback off verify off heading off echo off;
select count(1) from v$archived_log where
COMPLETION_TIME < sysdate-$KEEP_ARCHIVE_HOURS/24
and backup_count = 0 and name is not null;
While testing on several systems, I found that the above-mentioned query picked up some redo-logs (*.rdo) I can only assume that these redo logs were used for a DB recovery.
/u05/oradata/dmartdev/redo_01a.rdo120724-JUL-1125-SEP-11
/u15/oradata/dmartdev/redo_01b.rdo120724-JUL-1125-SEP-11
/u06/oradata/dmartdev/redo_02a.rdo120824-JUL-1125-SEP-11
/u16/oradata/dmartdev/redo_02b.rdo120824-JUL-1125-SEP-11
/u07/oradata/dmartdev/redo_03a.rdo120324-JUL-1125-SEP-11
[code]....
I have a few questions:
1) Is my assumtion correct about the .rdo files in the v$archived_logs table
2) I would hate to add special logic and look for something like name substr(name, *.arc) (pardon the syntax). Therefore, is there a way to get rid of these *.rdo files?
View 9 Replies
View Related
Mar 2, 2012
create table abc(
id number,
num varchar2(10),
name varchar2(10),
ref varchar2(10)
);
insert into abc values(1,'sss','cd','111');
insert into abc values(1,'sba','other','111');
insert into abc values(1,'svss','cd','111');
[Code]...
Quote:
The output:
ID CD OTHER
1svss,ssh,jhs,ssssba
2hvg,sss ggsss,hvgsss
I want the output not in coma seperated but in '||' seperated so I used translate function to replace coma by '||', but the problem I am facing that for column where values are enterd allready in coma seperated for eg:hvg,sss for ID 2, then its difficult to identify.
so I want the output in this format:
for eg if the values are entered: 'aaa', 'bbb,ccc'
ID CD OTHER
1 aaa||bbb,ccc ssss
I am using oracle 10g
View 2 Replies
View Related
Aug 16, 2013
I have a table which has two partitions (by range): first_half and second_half based on a column "INSERT_DAY".
I need to add sub partitions "SUCCESS" and "NONSUCCESS" based on the values of another column "STATUS" (subpartition by list) i.e. I need to transform my range partition to composite (range-list) partition. I do not wish to drop existing tables or partitions. What is the ALTER query for this?
View 10 Replies
View Related
Mar 28, 2010
As far as SQL is concerned, I am using oracle 9i and oracle sql *plus.
1.
This is a table 'spj'
SID PID JID QTY
---------- ---------- ---------- ----------
1 3 2 5
1 2 2 12
1 7 1 10
2 5 3 5
[code]...
I wish to count all the qty for each pid.This is what I have:
SQL> select pid,sum(qty) from spj group by pid;
PID SUM(QTY)
---------- ----------
1 5
2 12
3 29
4 10
5 5
6 6
7 10
and it seems to work fine. But I now I want to display another column in the table called 'pname' which is a primary key in another table 'p'. 'spj.pid' is the foreign key.
2. Another query I can't perform needs the table s
SID SNAME STATUS CITY
---------- -------------------- -------------------- -------------------
1 s1 y asansol
2 s2 y durgapur
3 s3 y durgapur
4 s4 y asansol
5 s5 y kolkata
6 s6 y asansol
7 s7 y tarakeshwar
the question says:
Quote: List supplier names(snames) who supply at least all parts supplied by supplier(sname) S2.
How may I achieve it.
View 5 Replies
View Related
Aug 26, 2010
I am running a query in Oracle that uses multiple joins. I want to return as a part of the select statement a column that contains the row number of the result.
Here is an example of my query;
SELECT *
FROM A_TABLE
INNER JOIN THEADM.A_TABLE_EXTENSION
ON THEADM.A_TABLE_EXTENSION.OBJID = A_TABLE.OBJID
INNER JOIN THEADM.A_TABLE_ENV
ON THEADM.A_TABLE_ENV.A_TABLE_ENV2A_TABLE = A_TABLE.OBJID
INNER JOIN THEADM.A_TABLE_LOG
ON THEADM.A_TABLE_LOG.A_TABLE_LOG2A_TABLE = A_TABLE.OBJID
WHERE CREATION_TIME > '01AUG10'
I want the resulting query (after the final inner join) to contain a column named 'Column' (or similar) that contains the number of the row.
I cannot re-sort the result set - there is no criteria by which to do this. (which is why I want the row number so badly... so that I can sort the result set however I want later and return to the original using this row number) The way that it is returned from the system is the way that I need it so using OVER ORDER BY won't work for me here. I also can only read the database, I cannot INSERT or add any tables.
View 11 Replies
View Related
Aug 6, 2013
a) What if i want to find out the Datatype of a specific column in the Table.
b) How do i find the Column Datatypes?
View 3 Replies
View Related
Sep 12, 2010
I would like to know how can i add a column in existing report. I try to change SQL by doing this it does add that new column into group but when i try to run report i can not see that changes . then i try to do some changes in paper layout but i am not able to bring the changes in out put. i am new to this FORMS Reports . do i have to create a group again i am how can i change the layout in that report.
View 4 Replies
View Related
Oct 23, 2012
I have a table A with a column B timestamp(6). The tables contains around 300000 rows..I have created index 'idx' on the column B.When i compare column 'B' with systimestamp, it does not use the index, whereas if i compare 'B' with sysdate it uses the index.
Eg :
select count(*) from a where b<=sysdate;
The above used the index 'idx' and executed in 1 second
select count(*) from a where b<=systimestamp;
The above does not use the index and executed in 19 seconds.
View 4 Replies
View Related
Aug 18, 2013
I have a table called cust_file, his table consists of a lot of columns (one of these columns called cus_tax) and have a lot of data,I use oracle 11g, I want to change the default value of the column cus_tax to be equal 1, I wrote
ALTER TABLE cust_file MODIFY(cus_tax DEFAULT 1); table alteredbut
after I inserted new data to test the operation, I found that the new record has a value
= null for the column cus_taxthen
I tested using the following query select
data_default from all_tab_columns where table_name='CUST_FILE' and column_name='CUS_TAX'; no rows selected...
Change the default value of the column cus_tax.
View 3 Replies
View Related
Aug 16, 2013
I have a SP for comparing 80 diff column values in 8 table pairs and it is taking a huge lot of time to process as I have to process around 10k records.
View 5 Replies
View Related
