Tag Info

Hot answers tagged

4

The book is assuming that PersonFriend is indexed on PersonID, but not on FriendID. It also seems to assume that Person indexes PersonID and Person independently. If this is the case, the first query comes back as {INDEX UNIQUE SCAN Person on Person => 'Bob' get back PersonID} {INDEX RANGE SCAN PersonFriend on PersonID => PersonIDs for Alice and Zack ...


4

There is nothing specific about SELECT here. There is no semantic difference between SELECT, select and SeLeCt. The issue being described is that if you issue these three semantically identical statements: SELECT thing FROM mytable; select thing from mytable; Select Thing From MyTable; the database treats them as three entirely different entities, ...


3

This kind of problem can be solved with the use of the LAG() function: WITH cte AS ( SELECT dept, term, LAG(dept) OVER (ORDER BY term) AS prev_dept FROM tableX ) SELECT dept, term FROM cte WHERE prev_dept <> dept OR prev_dept IS NULL ORDER BY term ; Test at: SQL-Fiddle


3

A dedicated uuid type is your best bet for PostgreSQL. Hard to say with other DBs - it's not impossible for someone to impliment a uuid type that's stored less efficiently than a simple byte type. Again in PostgreSQL, bytea would be a reasonable way to store UUIDs if you didn't have the uuid type. For other DBs it depends on how they store binary data. ...


2

Yes, you can. This is called tracing the Oracle optimizer. Doing so creates a trace file in which the optimizer dumps all reasoning done when composing a plan. An example of generating a trace for a specific SQL can be found here how to trace optimizer for specific SQL system wide - 10053 trace event You might need a little time to read it, given the ...


2

If you really want to normalize your design, here is a suggestion to do so. You should be able to create foreign key constraints. This is without the extra Instruction table since all documents will have the instruction details, stored in the Document table: Executor ---------- ExecutorID Document ---------- DocumentID ExecutorID Deadline Target -- other ...


2

The document that has the client/server compatibility matrix is 207303.1 Client / Server / Interoperability Support Matrix For Different Oracle Versions. (Oracle support account required) Oracle client version 12c is compatible with Oracle server version 11.2 (and below), so no your error isn't likely to come from that. Check that the listener is indeed ...


2

The answer is very simple. The ! prefix in sqlplus executes the following text as a command in the local shell. In your case it's executing the Unix hostname command locally. Query v$instance to check the name of the machine you're connected to. There are ways to execute local Unix commands via the database, but it's seen as a huge security hole, so I ...


2

3<4, I opt for option 2 approach. This is also what Oracle instructs us to do in for example this document: http://docs.oracle.com/cd/E11882_01/server.112/e23633/upgrade.htm#UPGRD12394 After you have installed the software for Oracle Database 11g Release 2 (11.2) and any required patches, Oracle recommends that you analyze your database before ...


2

In Oracle, empty string '' when assigned to a varchar2 type is NULL for all intents and purposes. In my SQLdeveloper, on a 12c database, the following query results in a (null) for every column. If you display to user a blank (a space, ' ') it would surely be displayed by all tools as a blank. SELECT NULL, '', CAST('' as varchar2(1)), CAST('' as char(1)), ...


2

In preference order: 4,1,2,3 Do not use UUIDs as the clustering key if using SQL server as, not only will it fragment badly, the clustering key is used in all non clustered indexes and you'd add those bytes to each index row. Fragmentation can be mitigated by using NEWSEQUENTIALID but usually prefer a bingint identity for your Clustering Key over a GUID to ...


2

Move control.dbf to control.dbf.damaged.20140706 connect / as sysdba show parameter control Verify here you have had only a single control file and not two or three of them. If you have had more than one control file, then this instruction is not for you. STARTUP FORCE NOMOUNT; CREATE CONTROLFILE DATABASE "XE" NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 ...


2

That figure is the share of the overall CPU utilization for each process. In your case, it means that that specific oracle process is responsible for 100% of your current CPU utilization - i.e., you have no other applications that are eating CPU. So 100% of your ~1-core load is generated by that specific Oracle process. There is an example in the ...


2

You seem to mixup the terminology for database and schema in an Oracle database. In Oracle up to 11g, a database is just a collection of files. Those files are logfiles, controlfiles, datafiles and tempfiles. Tablespaces are created using datafiles and you create tables and indexes inside a tablespace. If you want to create tables, or other schema objects, ...


2

Check the Oracle documentation here. There is a hash associated with queries in the shared pool (what Oracle calls the query cache) - so you could issue two - one with caps, one with lower case and check the hash and see if they're the same. [EDIT - removed rash speculation]


2

Yes, Oracle RAC is built using multiple servers sharing their memory - Cache Fusion - and they all access the same shared storage where the database resides. In Oracle an Instance is just the collection of processes that acts as an interface between client connections and the actual database. Since all storage is shared, when a server crashes, a surviving ...


2

Leaving the question aside why in the world you would want to stay on Oracle 10g, my preference to this would be the long way. I've done many "upgrades" this general way: Build a new empty 10.2.0.5 DB on your new server Identify the schemas that your application uses on the old server, including any accounts that an application uses that don't have any ...


2

Simple, but "dirty": with t as ( select 1 as id, 'Y' as received from dual union select 1 as id, 'N' as received from dual union select 2 as id, 'N' as received from dual union select 2 as id, 'N' as received from dual union select 2 as id, 'Y' as received from dual union select 3 as id, 'N' as received from dual union select 3 as id, 'Y' as ...


2

Instead of using so many JOINs to get the result, you might get better performance pivoting the data using an aggregate function with a CASE expression. Oracle 10g doesn't have a PIVOT function so you'd have to use this type of query (aggregate/CASE) if you aren't going to use multiple JOINs on your table. Since you have a limited number of questions ...


1

Add the predicate: AND NOT EXISTS ( SELECT 1 FROM role AS r_existing WHERE r_existing.role_name = 'prefix-' || role.role_name)


1

This means that the listener (the software that listens on the network for database connection attempts) doesn't know that your DB exists. Normally, when you start up an Oracle 11g DB, it'll automatically "register" with the listener within 60 seconds or so (I believe it's background process PMON responsible for this). You can force a listener registration ...


1

Start with (use the appropriate install syntax for your distro) sudo apt-get install libaio1 and if that doesn't do the trick, do sudo apt-get install libaio libaio-dev Paul...


1

All the information regarding statistics gathered with dbms_stats can be found on dba_tab_stats_history. Ref.: http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2103.htm#BGBBBGCC


1

Instead of the string literal VAR1 and VAR2, use the variables in your EXECUTE IMMEDIATE statement: execute immediate 'CREATE DATABASE LINK try_lnk CONNECT TO ' || VAR1 || ' IDENTIFIED BY ' || VAR1 || ' USING ''' || VAR2 || '''';


1

In Oracle (and other RDBMSs), when an SQL statement is run for the first time, it is cached and if somebody else wants to run a similar statement, the plan is taken from the cache - or shared pool. This is called soft parsing and is less costly than going to the trouble of redoing it entirely. So, the term "construct" in this context means an in memory ...


1

I am assuming that you want the Oracle optimizer to pick tableb as the driver table and for each row in tableb, have it run an index scan for tablea (Since tablea has 3GB of data). Your indexes start with col4, so I am guessing you are hoping that one of them will be used by the optimizer. One thing to point out is that in your WHERE clause you have both: ...


1

From the documentation: Full table scans are cheaper than index range scans when accessing a large fraction of the blocks in a table. Full table scans can use larger I/O calls, and making fewer large I/O calls is cheaper than making many smaller calls I suggest reading that entire page and then asking a more specific question if the optimizer is ...


1

This may be about the selectivity of your predicates. If a query uses an index it suffers the overhead of reading from disk the pages which constitute the index itself. If instead it performs a table scan it has the overhead of retrieving data it will ignore. The relative cost of these two options will depend on the selectivity of your index, how the ...


1

There's no single right answer for any performance question. The answer is always that query optimizers are very smart, to a point. So the most efficient design or query will depend on the number of records, what kind of indexes you have, how selective they are, and many, many more factors. If you want to avoid keeping both bounds of your ranges, you can ...


1

BASH: DBCA: command not found DBCA should be lower-case.



Only top voted, non community-wiki answers of a minimum length are eligible