Tag Info

Hot answers tagged

33

I've swapped between working on Oracle and SQL Server over the past few years, and wrote a blurb on going the other way here. There are a number of idiomatic and architectural differences, and various bits of terminology get used differently by the vendor and developer/DBA communities surrounding each product. Physical architecture SQL Server organises ...


32

With GROUP BY b,a the tuples (null, 1), (1,1), (2,1) and (17,1) would end up in four different groups. With GROUP BY coalesce(b,a) the tuples (null,1), (1,1), (2,1) and (17,1) would end up in the same group. If you want the "conditional" grouping, then yes, the version with coalesce is probably what you want.


29

One of the biggest benefit of using a materialized view is that Oracle takes care of keeping the data in sync. If you have a separate aggregate table, you are responsible for keeping the data synchronized. That generally requires a reasonable amount of code and a decent amount of testing and most organizations manage to make mistakes that leave holes that ...


27

Oracle and SQL Server have a number of architectural and idiomatic differences, and several key bits of terminology are used differently in the documentation. It's quite a few years since I did this, but some of the major idiomatic differences are: Oracle has no direct equivalent to tempdb. Global temp tables are persistent entities and you do not create ...


26

SYS: automatically created when Oracle database is installed automatically granted the DBA role has a default password: CHANGE_ON_INSTALL (make sure you change it) owns the base tables and views for the database data dictionary the default schema when you connect as SYSDBA Tables in the SYS schema are manipulated only by the database. They should never ...


25

Strictly, yes, the FROM clause of a SELECT statement is not optional. The syntax for SQL-99 details the basic SELECT statment, and the FROM clause doesn't have any square brackets around it. That indicates the standard considers it non-optional: SELECT [ DISTINCT | ALL ] {Column expression [ AS name ]} [ ,... ] | * FROM <Table reference> [ ...


21

From Wikipedia: The DUAL table is a special one-row table present by default in all Oracle database installations. It is suitable for use in selecting a pseudocolumn such as SYSDATE or USER. The table has a single VARCHAR2(1) column called DUMMY that has a value of 'X'. Thus, the dual table is a way to perform operations against what amounts to be an ...


20

First impressions Depending on your performance requirements, 100TB is a fairly aggressive data volume. If you want Oracle, you should check out their Exadata systems. Also, take a look at the offerings from Netezza or Teradata. With that volume of selects you might want to look at an OLAP based front end or at least fairly aggressive use of ...


18

It would seem that Oracle at one time had plans to give a different definition to VARCHAR than to VARCHAR2. It has told customers this and recommends against using VARCHAR. Whatever their plans were, as of 11.2.0.2 VARCHAR is identical to VARCHAR2. Here is what the SQL Language Reference 11g Release 2 says: Do not use the VARCHAR data type. Use the ...


17

You need to recreate the control file This post by Kaunain Ahmed describes the necessary steps: do: alter database backup controlfile to trace; extract the "create controlfile" command from the background-dump-destination tracefile. shutdown the DB. Change the DB-Name in your init.ora and change the init.ora Change the SID in the ...


16

Currently, the two are synonymous. VARCHAR is an ANSI standard data type but Oracle's implementation of the VARCHAR data type violates the ANSI standard by considering the empty string to be NULL (Oracle's implementation predates the ANSI standard). As Leigh points out, Oracle has stated that the semantics of the VARCHAR data type may change in the future ...


16

No, partitioning allows some table scans to be restricted to a particular partition. Indexes tend to be useless if you will be returning more that 2 to 4 percent of the table's data. If your selection criteria allows the query to be localized to particular partition, then the other partitions won't need to be scanned. It might be possible for the ...


16

Realistically, the requirement is crazy. Like all great crazy ideas, however, it is probably based on a nugget of potential reasonableness taken far out of context by people that have no understanding of the underlying rationale. It can be reasonable to design a database schema such that no NULL values are allowed. If you do that, however, you are ...


15

Use a common table expression (CTE) and a windowing/ranking/partitioning function like ROW_NUMBER. This query will create an in-memory table called ORDERED and add an additional column of rn which is a sequence of numbers from 1 to N. The PARTITION BY indicates it should restart at 1 every time the value of Val changes and we want to order rows by the ...


15

All of the platforms you have mentioned can run close to zero data loss configurations. All of them could be deployed in a configuration that will fail. Platform choice is one part of the puzzle. It will be your implementation of the platform that determines whether or not your requirements are met. MySQL Cluster Oracle RAC PostgreSQL High Availability ...


14

Assuming you can't change your structure, you can use a CASE statement in your ORDER BY: ORDER BY case cat when 'mgr' then 1 when 'dev' then 2 else 3 If you can change your structure, however, you can create a Category table including the category code and a ranking, join your sample table to the Category table and sort by this ranking.


14

Commants to the local instance execute on return. Multi-line commands to the server execute on semicolon Special commands as detailed in the SQL*Plus manual are the only ones that do not accept semi-colons. Wheras SQL Commands must end with a ; in order to be parsed by the server.


14

It will take marginally longer to generate the plan when there are more indexes to consider, but I doubt the difference would be measurably significant. The reasons for dropping an index does not list query performance. On the other hand, in general you shouldn't create indexes unless you know they will be used to make a query more efficient. From the ...


14

Here is a demonstration of a_horse_with_no_name's excellent+1 answer. SQL> WITH Data AS ( 2 SELECT level, DECODE(Level,3,NULL,1) A 3 , DECODE(level,2,NULL,4,2,1) B 4 FROM dual connect by level <=5 5 ) 6 SELECT A, B, count(*) FROM Data GROUP BY B, A; A B COUNT(*) - - ---------- 1 1 2 1 1 1 2 ...


14

This is an interesting question: When does Oracle really delete data physically ? The unit of data in Oracle is a block. Let's see what happens when we delete a row. Here's an example with a simple table on 11gR2 (see "How to dump Oracle Data Block?"): CREATE TABLE test_delete_data(id NUMBER,data VARCHAR2(100)); INSERT INTO test_delete_data VALUES (1, ...


14

There are problems that will occur if a session with a different date format runs the code. Statement Failure DROP TABLE t1; CREATE TABLE t1 AS (SELECT sysdate mydate FROM dual WHERE 1=2); ALTER SESSION SET NLS_DATE_FORMAT = 'MON-DD-RR'; INSERT INTO t1 VALUES ('01-02-12'); * ERROR at line 1: ORA-01843: not a valid month Bad Data ...


14

It's simple. MySQL has a single daemon that runs the database server. Within the server you can create any number of databases - these databases have no direct mapping to users. Oracle has a single database. When you create a user in an Oracle database, it also creates a Schema with the same name as the user that created it. This is equivalent to a ...


13

Your question doesn't require regular expressions: WHERE name LIKE 'A%' OR name LIKE 'B%' For Oracle, there's REGEXP_LIKE; Postresql, there's SIMILAR TO; mysql has REGEXP. One thing to remember though is not all regular expression engines are the same, so just because they support regular expressions doesn't mean they necessarily support word boundry ...


13

The logic with 'A' and 'B' might be "hidden" behind a virtual column on which you could do the partitioning: alter session set nls_date_format = 'yyyy-mm-dd'; drop table tq84_partitioned_table; create table tq84_partitioned_table ( status varchar2(1) not null check (status in ('A', 'B')), date_a date not null, date_b ...


13

The answer to your question can be found in the SQL Language Reference (excerpt follows). Creating Basic Tablespaces: Examples This statement creates a tablespace named tbs_01 with one data file: CREATE TABLESPACE tbs_01 DATAFILE 'tbs_f2.dbf' SIZE 40M ONLINE; It sound like you are new to Oracle databases. Oracle provides a wealth of ...


13

There are a few different approaches depending on the details of your batch process and why you're trying to view the uncommitted changes. 1) Oracle Workspace Manager is a tool that was originally designed to allow people developing Spatial applications to have the equivalent of extremely long-running transactions (i.e. transactions that may require ...


13

Oracle has a significantly higher barrier to entry than SQL Server. Does that make working with Oracle "harder" than SQL Server, well that depends on what is meant by "harder" and where you're setting the bar. I've very rarely encountered enterprise, Oracle backed systems, that didn't have dedicated Oracle DBAs/developers on the development team. I ...


13

No. All (standard) packages are written in PL/SQL. The DBMS engine itself is written in C Edit: Oracle does include a JVM which runs on the same machine as the database itself, but that is not used to run any "DBMS related" code. It's only there to run stored procedures/functions written in Java.


13

How do you log onto courgette? Would that username identify you? You can check that by running select sys_context('userenv', 'os_user') from dual; The USERENV namespace can retrieve a lot of different information about the user and their environment. Find out more.


13

There is No Valid Reason to use a magic value instead of NULL. This might be the thought process of someone creating this mess. They write something like this: SELECT c1, c2 FROM t1 WHERE c3 < 30; When this doesn't return the results they are expecting, they realize that it does not include NULLs and would need to write this: SELECT c1, c2 FROM t1 ...



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