If you are not familiar with tablespaces you may be wondering what the big deal about them is. Tablespaces are a logical addition to a database that helps maintenance, and potentially, can improve performance.



In Oracle and MySQL, a tablespace is a logical unit meant to store segments (i.e. tables and indexes). In Postgres, a tablespace is a physical unit. It is a symbolic link to a directory. Postgres does not allow tablespaces on operating systems that do not support symbolic links (such as windows).



The data file is the actual physical storage mechanism in Oracle and MySQL. Postgres stores tables in individual files.

Jack of all Games, the largest dedicated full line stocking video games distributor in the United States, has successfully implemented Oracle’s Demantra to revamp its demand planning and forecasting operations. The leading game distributor implemented Oracle’s Demantra to establish an efficient and reliable process for capturing and managing information while streamlining sales and ordering. Jack of all Games maintains exclusive distribution agreements with Take 2, Globalstar, Rockstar and Gathering of Developers to ensure that the hottest game titles reach national retailers as quickly as possible.

“Across the United States, we are shipping the latest games to all major retailers as well as many regional locations, video stores and all branches of the military.

El primeros nos ensamblan cubrirán se llaman un equijoin, también llamado un interno ensamblan. Aquí es donde una columna (o las columnas múltiples) en dos o más tablas empareja. Por nuestro ejemplo:

SELECT emp.ename, dept.dname
FROM emp JOIN dept
ON emp.deptno = dept.deptno
/

Qué este selecto dice es seleccionar la columna del ename de la tabla del emp junto con la columna del dname en la tabla del departamento. Ensamblar el emp y el departamento comparando las columnas del deptno en cada uno para la igualdad.

Puedo pensar en por lo menos tres otras maneras de escribir esta pregunta en Oracle, pero creo que ésta es la manera más clara y más intuitiva de hacer tan.

I remember the first time I downloaded MySQL. I think I was using Mandrake Linux. Anyway, the install was fairly painless but once it was installed, I had no clue how to run queries.



I was coming from an Oracle background and was used to SQL*Plus. I was also familiar with PostgreSQL and psql. For the life of me, I could not figure out how to get into MySQL.



So, for you developers and brand new users, you can easily start MySQL and start using it. This is not meant for a production installation, just for playing on your laptop or desktop.

I just downloaded Falcon and was running some tests. I was getting an error:

C:\MySQL\bin>mysqladmin version status proc
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'ODBC'@'localhost'
                             (using password: NO)'

To fix this error, just add a -u parameter, i.e.:

C:\MySQL\bin>mysqladmin version status proc -u root
mysqladmin  Ver 8.42 Distrib 6.0.2-alpha, for Win32 on ia32
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. 

I’m sure you’ve heard that it is a bad practice to embed SQL in your applications and that all direct SQL access should be encapsulated (or hidden) in stored procedures. I’ve had people ask me exactly what that means so below is a very simple example of that.
I am encapsulating a single table. This is really not that beneficial. Where this gets very powerful is when you are joining many tables or performing complex logic. You can use this method to hide schema complexity from any application that can query data.

I stumbled across a free MySQL magazine while reading the 64th edition of Pyhthian’s Log Buffer at Diamond Notes (a MySQL DBA Blog).
The magazine is MySQL Magazine. There are currently two editions, Summer 2007 and Fall 2007.

They’re in PDF format and, at least for a MySQL beginner like me, are very useful. The Summer 2007 edition has a very good “Securing MySQL Server” article. One improvement I would like to see om the site would be to have a table of contents in the HTML. That way I wouldn’t have to load the PDF just to see what’s inside.

It looks like Oracle is making the move towards appliances, albeit in a more componentized way.

Oracle today announced the Oracle Optimized Warehouse Initiative to help accelerate data warehouse deployments by offering a choice of optimized solutions that combine the performance, reliability and scalability of Oracle(r) Database with hardware and storage from industry leading manufacturers.

As part of this initiative, Dell, EMC and Oracle today introduced the initial Oracle Optimized Warehouse. (See today’s related press release at: http://www.oracle.com/corporate/press/2007_sep/dell-emc-oracle-warehouse.html ). Available through Dell, the Oracle Optimized Warehouse for Dell and EMC is comprised of Dell PowerEdge servers, EMC CLARiiON networked storage systems and Oracle Database.

“As the data warehousing market continues to grow and mature, Oracle is evolving to meet the changing needs of our customers,” said Ray Roccaforte, vice president of Data Warehousing and Business Intelligence Platform, Oracle.

Sign up for Live Webinars from EnterpriseDB. The December Data Warehouse one looks really good.

October 4, 2007 9AM ET & 2PM ET
Top 5 Ways to Supercharge Your Database
Abstract: – Join EnterpriseDB to learn 5 simple ways to supercharge your EnterpriseDB and Postgres databases to gain the best performance. From Dynatune™ to auto-vacuuming, there are simple ways to optimize your database without sacrificing availability and reliability

Speaker:
Jim Mlodgenski – Vice President, Worldwide Technical Services EnterpriseDB

October 10, 2007 1PM ET
Lowering TCO While Raising Revenues for Business Intelligence Applications
Abstract: – Join EnterpriseDB and JasperSoft to learn how to implement a fully functional BI architecture based on open source technologies that addresses the paradox of lowering your customer’s TCO while improving professional services revenue.

Speakers:
Jose Morales – Vice President, Business Development, JasperSoft,
Bill Doyle – Sr.Vice President, Business Development, EnterpriseDB

October 23, 2007 12PM ET
What is Enterprise-Class?

Are you following this case? Didn’t SAP admit wrong doing? Why is this going to court?

From ZDNet, Oracle vs SAP gets trial date.

Here are the key dates:

* Feb. 12, 2008: Another case management hearing.
* Nov. 13, 2008: The last date the judge will hear motions regarding the case.
* Feb. 9, 2009: The trial date.

Seems like a huge waste of resources to me.

oracle sap

VARCHAR2

VARCHAR2 es la secuencia del workhorse mecanografía adentro la base de datos de Oracle. VARCHAR2 es una secuencia de la longitud variable que debe tenerlo es longitud máxima declarada antes de usar. En Oracle 10g, una columna VARCHAR2 puede ser hasta 4000 octetos y puede ser hasta 32767 octetos en un programa de PL/SQL.

VARCHAR2 puede almacenar cualquier tipo de datos no-binarios. Dependiendo de la lengua definida en la base de datos (doble o multibyte), el número real de caracteres puede ser menos que el número de octetos.

¿Conjeturo que la primera pregunta está realmente, “cuál es una base de datos”? Una base de datos es una recogida de datos organizada. Los datos pueden ser textuales, como datos de la orden o del inventario, o pueden ser los cuadros, los programas o todo lo demás que se pueden almacenar en una computadora en forma binaria.

Una base de datos emparentada almacena los datos bajo la forma de tablas y columnas. Una tabla es la categoría de datos, como empleado, y las columnas son información sobre la categoría, como nombre o la dirección.

Algunas bases de datos tienen sistemas mínimos de la característica y almacenan solamente datos, mientras que otras incluyen lenguajes de programación, instalaciones y utilidades para apoyar usos del empresa-nivel como ERP y el almacenamiento de los datos.

Augusto Bott at Pythian recently posted a good entry detailing some differences between Oracle and MySQL from a MySQL DBA’s perspective, From MySQL to Oracle: A Few Differences. My viewpoint is exactly the opposite, I know Oracle extremely well but I have a lot of learning to do with MySQL.
Augusto ran into a SQL*Plus issue, where it is less than obvious where a SQL syntax error is. He could get around this error by using SQL Developer, Oracle’s free SQL Development IDE. SQL Developer can connect to and browse MySQL databases in addition to Oracle and SQL Server.

A good article on Motley Fool, discusses Oracle’s outstanding financial performance. I still think Oracle needs to do more to counter open source offerings but numbers like these are hard to argue with.

The numbers continue to speak for themselves at Oracle. First-quarter sales advanced 26% and earnings grew 28%. Better yet, new software license revenue grew 35%, well ahead of the 23% growth in license updates and product support and the 25% improvement in services revenue. Management explained it by noting that its installed base is growing, which only leads to lucrative product updates and related services down the road.

It looks like buying out the competition is having a doubly good effect.

A new Webinar about EnterpriseDB (not from me). Building a Highly Available Infrastructure for Your Database and Application.

It’s next wednesday, September 26, 2007 at 9am and 2pm Eastern. I guess it repeats. It’s an hour long.

Here’s the blurb from the email I received:

What does an hour of downtime cost you? Analysts report that just one hour of downtime can cost businesses millions of dollars, and some never recover.

What applications are mission critical today? In the past, only those applications that controlled finances were considered mission-critical.

Read Partitioning In MySQL, Part 1 before reading this post.



Partition pruning is the most critical performance component about partitioning. Partitioning can ease management of your data (for example the ability to just backup or restore a single partition or the ability to drop a no longer needed partition) but the main reason people add partitions is for performance.



Pruning helps performance by only reading the partitions that need to be read for a query. For example, if you have a table range partitioned by year that has partitions for the years 2000 through 2010.

Dubai Police has chosen Oracle as their database of choice.

Dubai Police has achieved remarkable returns on its technology investment as a result of deploying Oracle software solutions. Oracle built a robust foundation to automate and streamline Dubai Police’s messaging processes with its market-leading Oracle® Database software. The solution now serves more than 5000 internal users.

“As a major government entity, Dubai Police has a growing pool of internal and external IT users – employees and clients respectively. In line with Dubai Government’s e-government initiative, Dubai Police had a strategic need to migrate toward an enterprise-level IT infrastructure to enhance productivity and streamline operations,” said Colonel Ahmed Hamdan Bin Dalmook, manager e-services department, Dubai Police. “We wanted to organise and automate our daily correspondence, and align our business processes between departments.”

oracle oracle

This is just a quick overview of the availability of programming for Postgres. With Oracle, your choice in the database is Java or PL/SQL. In general, PL/SQL is the preferred language.
One of the things that attracted me to Postgres was the ability to choose one of many languages to program in. I usually choose PL/pgSQL but if I can’t do something with that, it’s nice to be able to fall back to TCL, Perl or one of the many other languages supported by Postgres.
Here is a (incomplete, I’m sure) list of supported languages:

• PL/pgSQL – Standard Procedural Language for Postgres
• PL/TCL – TCL, Tool Command Language, easy to learn but powerful
• PL/Perl – Practical Extraction and Report Language, has grown way beyond its origins
• PL/Java – Java, meh
• PL/PHP – PHP: Hypertext Preprocessor, over hyped language
• PL/Python – Object Oriented, dynamic language.

Nifty! Oracle® Database 11g Running on HP with Windows Sets New World Record.

Oracle announced a new world record price/performance result with the TPC-C benchmark running Oracle® Database 11g on Windows. With this result, Oracle now holds the top two record benchmark positions in the coveted Top Ten TPC-C price/performance category. Optimized for small, medium and growing businesses (SMBs), the combination of Oracle Database 11g and Windows on an HP ProLiant server provides an ideal platform to meet these companies’ individual business needs. This benchmark result further demonstrates Oracle’s commitment to providing customers of all sizes unmatched price/performance and scalability.

“Out of the gate, Oracle Database 11g sets a new price/performance bar for the industry and it will continue to excel as it matures,” said Juan Loaiza, senior vice president Systems Technology, Oracle.

I was browsing around the MySQL web site tonight and ran across some free webinars (recordings). Seeing as how I once did a podcast on Oracle partitioning, one webinar that jumped out at me was Partitioning in MySQL 5.1 and onwards.



I didn’t even know MySQL did partitioning. Here is a description of the webinar:




In this webinar we will provide further insight into MySQL partitioning, including:

• Introduction to MySQL Partitioning
• Linear Key Partitioning
• Partition Function
• Partition Options
• Information Schema for Partitions
• EXPLAIN PARTITIONS
• Partition Management
• Partition Pruning
• Partitioning and NULL Values
• Partitioning Implementation
• Partitioning for MySQL Cluster
• Future Roadmap for Partitioning
• Partitioning Limitations in 5.1

Sounds good. It’s a good presentation but kind of slow.

SQL, SEQUEL pronunciada, es la lengua de estándar para tener acceso a bases de datos emparentadas. SQL es una abreviatura para el lenguaje de interrogación estructurado. No dejar el hecho de que es un susto de la lengua tú. Usar su funcionalidad básica es muy simple.

Aun cuando SQL es un estándar, cada vendedor tiene sus propias extensiones y limitaciones. Oracle no es ninguna excepción. Hasta Oracle 9i, Oracle no apoyó estándar ensambla el sintaxis que fue apoyado por las bases de datos tan simples como MS-Access. Sin embargo, Oracle ha proporcionado siempre las extensiones que te hicieron una plataforma muy robusta para todo del acceso de usuario general a las preguntas ad hoc a los usos en tiempo real del alto rendimiento al almacenamiento de los datos.

En este artículo, cubriré el SQL básico.

News has been slow in the EnterpriseDB world lately. I haven’t posted in a while.

Bob Zurek is EnterpriseDB’s (fairly) new CTO. He is an ex-IBM exec and maintains his blog on the IBM DeveloperWorks blog area. He’s been blogging for a long time so I’m not sure how he worked out the move from IBM to EnterpriseDB and kept the same area.

Anyway, Bob posted an open letter to the Wall Street Journal about a reporter’s take on Open Source. Bob objected to the reporter, Walter K.

Do you remember Foxpro? xBase? Clipper? I’m an old clipper head from way back. Anyway, I ran across something in my recent internet travels that caught my fancy. It may have the longest name of any utility I have ever seen.
FoxPro PostgreSQL Import, Export & Convert Software will transfer tables to and from PostgreSQL and FoxPro.
I haven’t downloaded it yet so I can say how well it works but it’s a neat idea. I’m guessing it can handle DBF files so that would make it compatible with dBase, Foxpro, Clipper and may other tools.

Ireland based Netsure, a communications intelligence and analytics provider, will become part of Oracle’s Communications Global Business Unit, which delivers compelling communications-specific applications to service providers worldwide.

“The addition of Netsure’s products to Oracle’s comprehensive communications applications suite is expected to help Oracle’s customers improve network utilization, optimize capacity planning and financial modeling, and streamline end-to-end network lifecycle management,” said Oracle Senior Vice President and General Manager, Bhaskar Gorti. “In conjunction with Oracle’s ERP, supply chain, inventory management and network discovery solutions, Oracle plans to provide service providers the ability to improve their network ROI and increase their operational efficiency as they launch, deploy and profit from next-generation IP services.”

oracle netsure

I just found out that MySQL 6.0 with the Falcon Storage Engine is available as Alpha. Falcon adds ACID compliance, tablespaces and performance improvements. It’s supposed to also simplify administration but I’m not sure how MySQL can be simplified. It’s pretty simple now.
I’ve always thought that the ability to swap out the storage engine is the best feature of MySQL. It reminds me of the replaceable database drivers from my clipper days in the late 80s and early 90s.

From my reading about Falcon, it looks like one advantage will be the fact that Falcon has been engineered to take advantage of 64 bit architectures and large memory caches.

This is just a little peeve of mine. When you install EnterpriseDB, you can choose either a full production license or the free EnterpriseDB Express version. On my laptop, I want to run the express version. I don’t need it to use more than 1 gig of ram, more than 1 cpu or more than 6gb of disk. But I can’t even install it because my hardware exceeds those specs.

Oracle Express Edition has the same limitations as EnterpriseDB Express (and so does Microsoft SQL Server Express), but Oracle (and MS) limit their hardware usage by building the limits into the software.

Procedimientos CONTRA funciones

Los procedimientos son tradicionalmente el workhorse del mundo de la codificación y las funciones son tradicionalmente los pedazos más pequeños, más específicos del código. Generalmente si necesitas poner al día la carta de cuentas, escribirías un procedimiento. Si necesitas recuperar el código de la organización para una cuenta particular de GL, escribirías una función.

Aquí están algunas más diferencias entre un procedimiento y una función:

• Una función DEBE volver un valor
• Un procedimiento no puede volver un valor
• Los procedimientos y las funciones pueden ambos datos de vuelta en IN y IN OUT parámetros
• La declaración de vuelta en una función vuelve control al programa que llama y vuelve los resultados de la función
• La declaración de vuelta de un procedimiento vuelve control al programa que llama y no puede volver un valor
• Las funciones se pueden llamar de SQL, procedimiento no pueden
• Las funciones se consideran las expresiones, procedimiento no son

Eso está sobre todas las diferencias que puedo pensar en de la tapa de mi cabeza.

I think PL/SQL programmers who move to Postgres all run into the same thing, how do I run the procedure or function once I’ve created it?

Obviously, it’s easy when you are calling it from another stored procedure or function.  What most mean, is how do I call it from the command line?  That’s easy too.

Using my function and procedure from earlier posts, i.e. 10 Steps to Creating a Function In PostgreSQL Using PLpgSQL and Creating a Procedure In PostgreSQL Using PLpgSQL.

To call either the function or the procedure, you can run:

SELECT func_1();

Or

SELECT * FROM func_1();

You must include the ().

Same for the procedure.

SELECT proc_1();

Or

SELECT * FROM proc_1();

Of course, if you are using a GUI tool like PgAdmin III, it’s a somewhat different story.  You need to open the query window but the syntax will be the same.

Technorati Tags: postgres, postgresql, procedure, create, declare, plpgsql, function, code, develop

I recently wrote about creating a function in PL/pgSQL, 10 Steps to Creating a Function In PostgreSQL Using PLpgSQL.

Today, I am going to show you how to create a procedure.  You don’t really create a procedure, you use the same basic syntax as you do for a function.  The RETURNS keyword can get a little tricky as that keyword requirement changes based on what you are trying to do.  To start, I will create a procedure that functions the same as the function in the previous entry.  Then I will make some changes.

CREATE OR REPLACE FUNCTION proc_1(
  OUT out_parameter CHAR VARYING(25) )
AS $$
DECLARE
BEGIN
  SELECT datname
    INTO out_parameter
    FROM pg_database
    LIMIT 1;

  RETURN;
END;
$$ LANGUAGE plpgsql;

If you compare this to the function from the previous entry some things jump out right away.  There is no RETURNS statement.  Postgres is able to determine the return type from the OUT parameter.

The DECLARE keyword is completely optional.

For, I guess, about the last week or so, there has been an ongoing debate on the Advocacy email list about renaming the software from PostgreSQL to Postgres or something else.

Some people have very strong opinions about it. Personally, I don’t see what the big deal is. The only people who really have an issue are the people in that (hacker) community. CEOs don’t care what it’s called. No developer I know cares what it’s called.

Just to clarify, when I say developer, I mean a person who develops software for business use.